COP4710: Database Systems

Course Description

COP4710 Database Systems is the course on how data is modelled, stored, queried and kept correct — the foundation under essentially every application that remembers anything.

The course is offered at approximately five Florida institutions, including Florida International University, Florida State University, the University of Central Florida, the University of South Florida and the University of West Florida.

The University of West Florida places it in the College of Science and Engineering, Department of Computer Science at 3 semester hours, requires COP 2334 or COP 2253 or COP 2830, and describes an introduction to database systems and database management system architectures in which various database models are discussed with an emphasis on the relational model and relational database design, with case applications using fourth-generation languages such as SQL. It is offered concurrently with the graduate course COP 5725, with graduate students assigned additional work. Florida International University titles it Database Management at 3 credits, covering logical aspects of databases including relational, entity-relationship and object-oriented data models, database design, SQL, relational algebra, tuple calculus, domain calculus, and physical database organisation, with a prerequisite of COP 3337 or COP 3804 and a note that additional fees apply.

The two descriptions together show the course's characteristic shape: a theoretical spine with a practical surface. FIU's inclusion of relational algebra, tuple calculus and domain calculus is the theory — the formal query languages that give SQL its meaning and that make it possible to prove one query equivalent to another. UWF's emphasis on case applications using SQL is the practice. Courses vary in how much weight they give each, and a student should know which version they are taking.

What makes this course genuinely important, rather than merely required, is that it is the one where students learn that design decisions have consequences that outlive the code. An application can be rewritten in a weekend. A database schema, once it holds production data, is extremely difficult to change — migrations are risky, downtime is expensive, and every application touching the data has to change with it. Getting the model right at the start is one of the highest-leverage activities in software engineering, and this is the course that teaches it.

The second thing worth knowing is that the relational model is unusually well founded. It rests on set theory and first-order logic, its query languages have a formal semantics, and normalisation is a body of theorems rather than a set of conventions. That is rare in computing, where much practice is empirical. Students who learn the theory find that the practical rules stop being arbitrary — third normal form is not a style preference, it is a statement about functional dependencies and the anomalies that follow from them.

And the third: this material has not dated. The relational model was proposed in 1970, SQL shortly after, and both remain the dominant approach more than fifty years later, through several waves of predicted replacement. A student who learns SQL properly has acquired a skill with an unusually long demonstrated half-life.

Learning Outcomes

Required Outcomes

• Explain what a database management system provides and why applications use one rather than files.
• Explain data independence and the three-schema architecture.
• Construct an entity-relationship model from requirements, including entities, attributes, relationships, cardinality and participation.
• Map an ER model to a relational schema correctly.
• Explain the relational model — relations, tuples, attributes, domains, keys — and the integrity constraints it supports.
• Apply relational algebra and explain its relationship to SQL.
• Write SQL fluently — queries with joins, aggregation, grouping, subqueries and set operations; data definition; data modification.
• Explain and apply functional dependencies and normalisation through at least third normal form and BCNF, and explain the anomalies each addresses.
• Justify a decision to denormalise and state its costs.
• Explain indexing and physical storage, and predict the effect of an index on a query.
• Explain query processing and optimisation at a level sufficient to reason about performance.
• Explain transactions and the ACID properties.
• Explain concurrency control — locking, serialisability, isolation levels and deadlock.
• Explain recovery — logging, checkpointing and the restoration of a consistent state after failure.
• Apply database security basics — access control, privileges, and the prevention of SQL injection.
• Connect an application to a database and explain the interface layer.
• Design, implement and populate a working database for a stated problem.

Optional Outcomes

• Explain and use NoSQL systems and justify the choice against a relational alternative.
• Explain distributed databases, replication, partitioning and the CAP theorem.
• Explain data warehousing, OLAP and the analytical/transactional distinction.
• Apply stored procedures, triggers and views.
• Apply object-relational mapping and explain its trade-offs.
• Explain big data processing frameworks in outline.
• Explain spatial, temporal or graph data models.
• Explain database administration tasks — backup, tuning, capacity planning.
• Address data privacy and regulatory compliance in database design.

Major Topics

Required Topics

• Why databases exist. The problems with file-based storage — redundancy, inconsistency, concurrent access, atomicity, security; what a DBMS provides; data independence and the three-schema (external, conceptual, internal) architecture; the historical models — hierarchical, network — and why the relational model displaced them; database users and roles; the position of the DBMS in a system.
• Conceptual design: the entity-relationship model. Entities, attributes and their types; relationships, cardinality and participation constraints; weak entities; ER diagrams and their notations, which vary and cause needless confusion; the enhanced ER model — specialisation, generalisation and inheritance; the discipline of eliciting a model from a written requirement, which is the part students find hardest because the requirement is ambiguous and the modelling decision is a judgement; mapping ER to relations, algorithmically, including the treatment of many-to-many relationships and of multivalued attributes.
• The relational model and its formal basis. Relations as sets of tuples; schema versus instance; keys — superkey, candidate, primary, foreign; integrity constraints — domain, entity, referential — and referential actions on delete and update; NULL and three-valued logic, which is a genuine trap: NULL = NULL is not true, aggregates ignore NULLs, and NOT IN with a NULL in the subquery returns no rows — the source of more silently wrong queries than any other single feature.
• Relational algebra and the formal query languages. Selection, projection, union, difference, Cartesian product; the derived operations — join in its several forms, intersection, division; the point of learning it: SQL is a surface syntax over these operations, and query optimisers reason in algebra, so a student who knows the algebra can predict what an optimiser will do; relational calculus — tuple and domain — and the notion of relational completeness; equivalence of expressions, which is the theoretical basis of optimisation.
• SQL, in depth, because it is the course's most immediately useful content. DDL — creating tables, constraints, data types, altering schemas; DML — insert, update, delete; queries — SELECT structure, WHERE, ORDER BY; joins — inner, left, right, full, self and cross — and the standing confusion between them; aggregation — GROUP BY and HAVING, and the rule about what may appear in a SELECT with GROUP BY; subqueries, correlated and uncorrelated, EXISTS and IN; set operations; views and updatability; common table expressions and window functions, which are now standard and are how a great deal of analytical SQL is written; transactions in SQL; privileges and GRANT.
• Normalisation. The update, insertion and deletion anomalies that motivate it, taught first so the theory has a purpose; functional dependencies, closure and Armstrong's axioms; determining candidate keys; 1NF, 2NF, 3NF and BCNF, each defined in terms of dependencies rather than as a recipe; multivalued dependencies and 4NF in outline; lossless-join and dependency-preserving decomposition; denormalisation as a deliberate, justified trade of integrity risk for read performance — a decision rather than a shortcut, and one that must be documented.
• Physical design and indexing. Storage, pages and the reality that disk (or SSD) access dominates cost; B+ trees and why they are the near-universal index structure; hash indexes; clustered versus non-clustered; composite indexes and column order; when an index helps and when it hurts — indexes accelerate reads and slow writes and consume space, so indexing everything is a recognisable beginner error; selectivity; covering indexes.
• Query processing and optimisation. How a query becomes an execution plan; join algorithms — nested loop, sort-merge, hash — and when each is chosen; cost-based optimisation and statistics; reading an execution plan, which is the single most practically valuable skill in this topic and the one that separates a developer who can fix a slow query from one who guesses; common causes of poor performance — missing index, function applied to an indexed column, implicit type conversion, a query returning far more rows than needed.
• Transactions and concurrency. The transaction concept; ACID — atomicity, consistency, isolation, durability — with each explained by the failure it prevents; schedules and serialisability; two-phase locking; deadlock, detection and prevention; isolation levels and the anomalies each permits — dirty read, non-repeatable read, phantom — and the practical point that most production systems do not run at serialisable isolation, so a developer must know what their level permits; multiversion concurrency control; optimistic concurrency.
• Recovery. Failure classes; the write-ahead log; undo and redo; checkpointing; the ARIES approach in outline; backup strategies and the distinction between backup and replication; the point that durability is a promise the system makes and that understanding how it is kept is what lets you reason about what survives a crash.
• Security and the application boundary. Authentication, authorisation and the principle of least privilege; roles and privileges; views as a security mechanism; SQL injection — how it works, why string concatenation is the cause, and parameterised queries as the fix — which is examinable, is asked in interviews, and remains a leading cause of real breaches decades after being solved; encryption at rest and in transit; auditing; data privacy and regulatory constraints as design inputs.
• The application interface. Connecting from a program — JDBC, ODBC or a language-native driver; connection pooling; the impedance mismatch between relational and object models; object-relational mapping and its trade-offs, including the N+1 query problem, which is the most common performance failure in ORM-based applications; prepared statements.

Optional Topics

• NoSQL — document, key-value, column-family and graph stores; the CAP theorem and eventual consistency; when the relational model is genuinely the wrong choice.
• Distributed databases, replication, sharding and consensus.
• Data warehousing, star schemas, OLAP and the OLTP/OLAP distinction.
• Stored procedures, functions and triggers.
• Big data frameworks and columnar analytical stores.
• Spatial, temporal, time-series and graph data.
• Database administration — tuning, capacity planning, high availability.
• Cloud-managed database services.
• Data governance, privacy regulation and retention.

Resources & Tools

• Fundamentals of Database Systems by Elmasri and Navathe (Pearson) — the most widely adopted text and the likeliest assignment; thorough on ER modelling and normalisation.
• Database System Concepts by Silberschatz, Korth and Sudarshan — the other standard; stronger on the systems internals (transactions, recovery, query processing) and freely available from the authors' site in recent editions.
• Database Systems: The Complete Book by Garcia-Molina, Ullman and Widom — the most theoretically rigorous of the three.
• SQL Queries for Mere Mortals by Viescas — the best practical SQL-only book for building fluency; SQL Performance Explained by Markus Winand, and his free companion site Use The Index, Luke!, which is the clearest explanation of indexing available anywhere and is free.
• Database systems to practise on, all free:
  • PostgreSQL — free, standards-compliant, powerful, and the best default choice for learning; what you learn transfers.
  • SQLite — zero configuration, a single file, ideal for exercises; MySQL/MariaDB, widely deployed; SQL Server Express and Oracle Express, free editions of the enterprise systems.
  • DB Fiddle, SQLite Online and similar browser sandboxes — no installation, useful for quick practice.
• Practice, which is how this course is actually passed: SQLZoo, PostgreSQL Exercises, LeetCode's database section and HackerRank SQL — all free, all graded, and SQL fluency is built by writing several hundred queries, not by reading about them.
• Tools: DBeaver (free, works with everything), pgAdmin, MySQL Workbench; diagramming with dbdiagram.io, draw.io or Mermaid's ER syntax.
• Reading worth doing: Codd's 1970 paper, "A Relational Model of Data for Large Shared Data Banks" — short, readable, and one of the most consequential papers in computing; Designing Data-Intensive Applications by Martin Kleppmann, which is beyond the course and is the book to read next if this material interests you.

Career Pathways

Database competence is one of the most broadly demanded skills in computing, and unusually, it is demanded outside computing as well.

• Software Developers (SOC 15-1252) — the largest destination. Nearly every application has a database behind it, and SQL is a standard technical interview topic for general software roles.
• Database Administrators and Architects (SOC 15-1242, 15-1243) — the direct speciality: design, performance, availability, backup and security.
• Data Engineers (SOC 15-1243 and adjacent) — building pipelines and warehouses; one of the fastest-growing and best-paid areas in the field, and this course is its foundation.
• Data Scientists and Data Analysts (SOC 15-2051, 15-2041) — SQL is the single most-used tool in practical data work, ahead of any statistical language, and analysts who cannot write it are limited to whatever someone else extracts for them.
• Business Intelligence developers and analysts — reporting, dashboards and warehouse modelling.
• Backend and full-stack engineers, site reliability engineers, and cloud engineers managing database services.
• Information Security Analysts (SOC 15-1212) — database security, access control and injection prevention.
• Health informatics and clinical analytics (SOC 15-1211 adjacent) — a large Florida employment area given the size of the state's health systems.
• Systems Analysts (SOC 15-1211) — requirements and data modelling.
• Non-technical roles that pay for SQL — operations, finance, marketing and product analytics. SQL is the most transferable single skill in this course, and it opens roles that do not have "developer" in the title.

The Florida picture. Employment concentrates in healthcare informatics (AdventHealth, Orlando Health, BayCare, Baptist, Jackson Health, UF Health — all with substantial data operations), financial services in Miami, Tampa and Jacksonville, defence and simulation in Orlando, hospitality and travel (Disney, Universal, the cruise lines) which run some of the largest transactional systems in the state, state government in Tallahassee, and a growing startup sector in Miami.

The advice, and it is concrete. Become genuinely fluent in SQL rather than merely passing the course — it is asked in interviews, it is used daily, and it has retained its value for fifty years. Build one real database end to end: model it, normalise it, index it, load real data and write queries against it, and put it on GitHub. That project is worth more in an interview than the grade, and the conversation about why you modelled it the way you did is exactly what an interviewer wants to have.

Special Information

⚠ Prerequisites differ in level, and the difference is worth checking

Institution	Title	Prerequisite
UWF	Database Systems	COP 2334 or COP 2253 or COP 2830 — a first programming course
FIU	Database Management	COP 3337 or COP 3804 — a second, object-oriented programming course

Both are 3 credits and cover the same material. UWF's chain is the lighter one — a single introductory programming course — which makes the course accessible earlier and to students outside computer science. FIU gates a level higher.

The practical consequence is about what the course can assume. A section gated on introductory programming will teach the application-interface material more gently; a section gated on the second programming course can assume object-oriented design and move faster into ORM and application integration. Neither is better, but a transfer student may arrive having satisfied one chain and not the other.

What you actually need regardless: the ability to program in some language, comfort with basic data structures, and logical reasoning about sets and conditions, which is what SQL is. A student who found discrete mathematics congenial will find this course easy; the relational model is applied set theory and first-order logic.

⚠ FIU notes that additional fees apply — check whether your section carries a course fee for database or cloud resources.

⚠ Concurrent graduate offering — what it means for you

UWF states that COP4710 is offered concurrently with COP 5725, the graduate database course, with graduate students assigned additional work.

This is a common and generally favourable arrangement. The lectures are pitched at a level that serves both audiences, which usually means more depth than a purely undergraduate section — the transaction and query-optimisation material in particular tends to be treated more seriously. Undergraduates are assessed against undergraduate expectations, so the additional graduate work is not yours.

Two practical notes. If you are considering graduate study, this is a course where taking the material seriously is directly visible to a faculty member who teaches at that level — a useful relationship to have when you need a recommendation. And if your institution permits it, a strong undergraduate can sometimes negotiate doing the graduate assignments; it is worth asking.

⚠ Title variation and what it signals about emphasis

UWF's "Database Systems" and FIU's "Database Management" both cover the same ground, but the descriptions differ informatively. FIU names relational algebra, tuple calculus and domain calculus explicitly — the formal query languages — which signals a more theoretical treatment. UWF emphasises case applications in SQL, signalling a more applied one.

Both matter, and a student should notice which they are getting. The theory is what makes normalisation and optimisation intelligible rather than arbitrary; the practice is what gets you through an interview. If your section is light on one, supplement it — the free SQL practice sites cover the applied side, and the Silberschatz text covers the theory.

Position in the curriculum

COP4710 is an upper-division computer science course, normally junior or senior year, and is required in essentially every Florida computer science and information technology degree. It is also a common requirement or elective for information systems, software engineering, data science, cybersecurity and business analytics programmes.

It follows the programming sequence and data structures, and pairs with software engineering, web development, operating systems, and — increasingly — data mining and machine learning courses, all of which assume you can get data out of a database. It is a prerequisite for database administration and advanced database courses where those exist; UWF's COP 4723 Database Administration requires it.

Course format and workload

Taught as a lecture with substantial programming and query assignments, almost always with a term project in which a team designs and implements a database for a stated application. Assessment typically weights the project and assignments heavily alongside examinations.

Expect six to ten hours a week outside class. The conceptual load is moderate; the SQL fluency requires volume, and the project consumes most of the back half of the term.

⚠ Three practical warnings.

• The team project is where the marks are lost. Database projects fail on coordination more than on technical difficulty — agree the schema early and in writing, because a schema change midway invalidates everyone's queries. Use version control for the SQL, which teams routinely fail to do.
• Install your database system in week one, not the night before the first assignment. Setup problems are common and are not interesting.
• Write queries every week from the start. SQL fluency is cumulative and cannot be acquired in a fortnight; students who defer it find that the project, which assumes fluency, becomes twice as hard.

⚠ The errors this course exists to prevent

These recur reliably and are worth recognising in advance:

• NULL handling. NULL = NULL is not true; NOT IN with a NULL in the subquery returns nothing; aggregates skip NULLs while COUNT(*) does not. This produces queries that run, return results, and are wrong — the worst category of error.
• Joining without a join condition, producing a Cartesian product and a very large, very slow, entirely wrong result.
• Confusing WHERE and HAVING, and misunderstanding what may appear in a SELECT alongside GROUP BY.
• Designing without normalising, then discovering the update anomalies in production.
• Over-normalising and producing a schema requiring eight joins for a common query — the opposite error, and a real one.
• Indexing everything, which slows every write and helps almost nothing.
• String-concatenated SQL, which is how SQL injection happens. Parameterised queries, always.
• Assuming the default isolation level is serialisable. It generally is not, and code written on that assumption has race conditions that appear only under load.

Articulation and transfer

COP4710 carries the same SCNS number across Florida public institutions and SCNS equivalency governs transfer of the credit. As an upper-division course it does not appear in A.A. programmes, though the lower-division programming prerequisites transfer cleanly from the state colleges.

Two notes. The prerequisite level difference above means a transfer student may need an additional programming course. And where the course is being used toward an ABET-accredited computing degree, the receiving department will check the coverage against its own curriculum requirements; keep the syllabus. Keep the project too — in computing, a repository is better evidence than a transcript, for transfer conversations and for hiring alike.

AI Integration

Database work is one of the areas where these tools are most immediately capable and where the failure modes are most consequential, because a wrong query returns a plausible answer rather than an error.

Where the tools genuinely help. Writing a first-draft query from a description, which is fast and usually close. Explaining an unfamiliar query — being handed a 200-line legacy SQL statement and asking what it does is a legitimately excellent use. Explaining an execution plan. Generating test data. Translating between SQL dialects, which is tedious and mechanical. And suggesting an index for a slow query, as a starting hypothesis.

⚠ Where they fail, and the first one is the serious one.

A generated query that returns results is not a correct query. This is the central danger and it is specific to this domain. A subtly wrong join, a missing condition, or a mishandled NULL produces output that looks entirely reasonable — and if the query feeds a report, a bill or a clinical dashboard, the error propagates silently. The defence is the one this course teaches: know what the query should return, check the row count, test against a small dataset where you can verify the answer by hand, and read the query rather than the output.

Schema-specific reasoning requires the schema. Without knowing the actual tables, keys, cardinalities and data distribution, generated advice about modelling or indexing is generic. The cardinality of a relationship — whether a customer can have more than one address — is a domain fact no tool can supply, and getting it wrong produces a schema that cannot represent the business.

Performance advice needs measurement. Suggested indexes may be redundant with existing ones, or may not be used by the optimiser at all. Measure with an execution plan; do not add an index because a tool suggested it.

Security is where generated code is most dangerous. Generated application code has repeatedly been shown to include string-concatenated SQL, which is an injection vulnerability. Any generated database code must be read for parameterisation before it goes anywhere near production.

What is genuinely changing in the field. Natural-language-to-SQL is a real capability and is being built into analytics products, which is a genuine democratisation of data access — and it means the person who can verify a generated query is now more valuable than the person who could only write one. Query optimisers have used cost models and statistics for decades and are increasingly learned; vector databases and embedding storage have become a mainstream category; and a great deal of routine data-pipeline code is now generated and reviewed rather than written.

The implication for what to learn. Writing a simple SELECT has become a commodity skill. What has not: designing a schema that will still be right in five years, knowing why a query is slow, understanding what an isolation level actually guarantees, and being able to say that a generated query is wrong and why. Those are exactly the parts of this course that look most like theory, which is a reason to take the theory seriously rather than to treat it as the part to survive.

Academic integrity. Read your instructor's policy; computing departments vary widely and many now permit disclosed use. The point specific to this course: the assignments build the query-writing fluency that interviews test in a room with no tools, and the project builds the design judgement that is the course's real content. A student who generates their queries can produce output and cannot debug it — which is the position they will be in on the first day of the job.