Applied Database II

Course Description

Applied Database II covers the examination of relational and other database systems along with advanced SQL capabilities. Topics include development of stored procedures and functions, decision support systems, transaction management and theory, with coverage of additional advanced database subjects. Programming knowledge is a prerequisite for enrolment.

Within the SCNS taxonomy, COP is the Computer Programming prefix. Daytona State publishes this at 3 credits, offered spring, with COP4708 as prerequisite. ⚠ The single term of offering, combined with COP4708 preceding it, is worth planning around.

The transaction management content is the intellectually serious part of this course, and it is the part that separates people who use databases from people who understand them. Concurrency is genuinely hard: multiple users reading and writing the same data simultaneously produces failure modes — lost updates, phantom reads, deadlocks — that do not appear in testing, appear intermittently under load, and cannot be reproduced on demand. Knowing what isolation levels actually guarantee is what lets you reason about them.

Daytona State does not publish a lecture and laboratory split for this course. Its computing and office systems courses are unsuffixed and run at the standard 15 contact hours per credit — CEN4010, CEN3722, CNT2402, OST2401 and CGS1570 are all live at 3 credits and 45 hours. This course is priced at that convention.

Learning Outcomes

Required Outcomes

• Describe advanced features of the relational model.
• Write advanced SQL, including window functions and common table expressions.
• Write recursive queries.
• Use set operations and describe their semantics.
• Write complex analytical queries over grouped data.
• Develop stored procedures and describe their appropriate uses.
• Develop user-defined functions.
• Use control flow, variables, and error handling in database code.
• Develop triggers and describe the risks of using them.
• Describe transaction theory and the ACID properties.
• Describe isolation levels and what each actually guarantees.
• Identify concurrency anomalies and the isolation level that prevents each.
• Describe locking, blocking, and lock escalation.
• Diagnose and resolve deadlocks.
• Describe optimistic and pessimistic concurrency control.
• Describe recovery, logging, and how a database survives failure.
• Describe backup strategies and recovery models.
• Design and query for decision support and analytics.
• Describe data warehousing and dimensional modelling.
• Describe OLAP concepts and aggregation.
• Describe non-relational database models and when they suit a problem.
• Describe distributed databases and replication at an introductory level.
• Tune queries and describe the optimiser's behaviour.
• Apply database security, roles, and permissions.

Optional Outcomes

• Describe database administration in depth.
• Describe high availability and clustering.
• Describe cloud database services.
• Describe data governance and quality management.
• Describe graph and time-series databases.
• Prepare for a database vendor certification.

Major Topics

Required Topics

• Advanced relational features
• Window functions and common table expressions
• Recursive queries
• Set operations
• Analytical queries
• Stored procedures
• User-defined functions
• Control flow and error handling
• Triggers and their risks
• Transaction theory and ACID
• Isolation levels and guarantees
• Concurrency anomalies
• Locking and blocking
• Deadlock diagnosis
• Optimistic and pessimistic concurrency
• Recovery and logging
• Backup strategies and recovery models
• Decision support and analytics
• Data warehousing and dimensional modelling
• OLAP and aggregation
• Non-relational models
• Distributed databases and replication
• Query tuning and the optimiser
• Security, roles, and permissions

Optional Topics

• Database administration
• High availability and clustering
• Cloud database services
• Data governance and quality
• Graph and time-series databases
• Vendor certification

Resources & Tools

• Build things and keep them. In computing the portfolio is the qualification — a public repository of working projects with readable code and a clear README does more for employment than a transcript.
• Git and a public repository host — free; version control is expected of every candidate, and using it from your first course is the cheapest habit to acquire.
• Free-tier cloud accounts (AWS, Azure, Google Cloud, Oracle) — real infrastructure to practise on at no cost; set a billing alert before you start.
• Virtualisation and containers — VirtualBox and Docker are free, and a broken lab you can rebuild in a minute is what makes experimentation cheap.
• Official documentation — language, database, and framework docs are authoritative and version-correct in a way that copied answers are not. Learning to read documentation is the skill that separates practitioners.
• Vendor certifications — Microsoft, AWS, Oracle, CompTIA and Cisco credentials are named by employers in job advertisements; many have free training paths and student pricing.
• Your instructors and the lab — supervised time on real systems is the part you cannot get from a video.
• Internships and co-op placements — the strongest single predictor of employment at graduation. Start looking a year before you think you should.
• Your database's transaction and isolation documentation — free and essential; isolation levels differ meaningfully between products, and assuming they behave the same is a real source of bugs.
• PostgreSQL and SQL Server sample databases — free, realistically messy, and much better practice than tidy exercises.

Career Pathways

• Software developer — SOC 15-1252.
• Computer systems analyst — SOC 15-1211; the requirements-and-design role.
• Database administrator and architect — SOC 15-1242 and 15-1243.
• Data analyst, business intelligence developer, and data engineer — strong demand and a common entry route for people who like data more than application code.
• Network architect — SOC 15-1241; and network and systems administrator, SOC 15-1244.
• Information security analyst — SOC 15-1212; strong growth, and systems or database work is the usual route in.
• Data scientist and machine learning practitioner — SOC 15-2051.
• Systems integration and enterprise applications — frequently overlooked by students and consistently well paid.
• Florida's defence, space, and simulation sector, plus healthcare, hospitality, financial services and a large public sector — ⚠ many defence roles require U.S. citizenship and some a security clearance.
• Managed service providers and consultancies — broad exposure quickly, and a common early career step.
• Continue to a bachelor's or graduate degree — Daytona State's computing bachelor's programmes are the direct route.

Special Information

⚠⚠ Concurrency bugs do not reproduce — which is why you reason about them instead

• Concurrency failures appear under load, intermittently, in production, and cannot be reproduced on demand. That makes them the class of bug you cannot debug your way out of — you have to understand what the database guarantees.
• Know what each isolation level actually prevents, and which anomaly it permits — dirty reads, non-repeatable reads, phantoms, and lost updates each have a level that stops them and a cost for doing so.
• ⚠ Higher isolation is not simply better. It buys correctness with concurrency, and an over-isolated system serialises itself into unusability under load.
• ⚠⚠ The lost update is the one that bites applications. Two users read the same row, both modify it, and the second write silently discards the first — and nothing errors, so nobody knows. Row versioning or explicit optimistic concurrency checks are the defence.
• Keep transactions short. A transaction held open across a user interaction, a network call, or a file operation holds locks for that entire time and is a classic cause of production blocking.
• Access objects in a consistent order across your code. Deadlocks arise when two transactions take the same locks in opposite orders, and consistent ordering prevents most of them outright.
• Expect deadlocks and handle them. The database will choose a victim and roll it back — the application should detect that and retry rather than surfacing an error to the user.
• ⚠ Do not solve blocking by reading uncommitted data, which returns rows that may never exist — it silences the symptom and produces wrong answers instead.
• Test under concurrency deliberately. Run the operation from several sessions at once; single-user testing proves nothing here.

⚠ Stored procedures and triggers — powerful, and easy to make unmaintainable

• Stored procedures have genuine advantages: they keep logic near the data, reduce round trips, allow permissions to be granted on the procedure rather than the tables, and can be tuned independently of the application.
• ⚠ They also fragment the system. Business logic split between application code and database code is harder to find, harder to test, and frequently outside version control — put database code in the repository like any other source.
• ⚠⚠ Triggers are the sharpest tool here. They fire invisibly, so a simple INSERT can cascade into changes nobody reading the calling code would predict — and debugging a system with layered triggers is genuinely miserable.
• If you use triggers, keep them minimal, documented, and few. Auditing and integrity enforcement are defensible uses; business workflow generally is not.
• Beware triggers that fire other triggers. Recursion and unexpected ordering produce behaviour that is very hard to reason about.
• ⚠ Remember set-based thinking. A procedure that loops row by row over a result set is usually replaceable by a single statement that runs orders of magnitude faster — this is the most common performance mistake made by programmers new to SQL.
• Handle errors explicitly in database code, and make sure a failure rolls back rather than leaving a half-completed change.
• Dynamic SQL inside a procedure reintroduces injection risk — parameterise it exactly as you would in application code.

⚠⚠ SQL injection — decades old, still ubiquitous, and trivially preventable

• SQL injection remains among the most damaging and most common vulnerabilities in software, and it exists because a program built a query by joining strings together, one of which came from a user.
• ⚠⚠ The fix is parameterised queries, and it is not optional. Pass values as parameters and let the driver handle them — never concatenate or interpolate user input into SQL, not for a small internal tool, not for a prototype, not once.
• Escaping input yourself is not a substitute. Hand-rolled escaping is repeatedly defeated by encoding tricks that parameterisation is immune to by construction.
• ⚠ Stored procedures are not automatically safe — a procedure that builds dynamic SQL from its arguments is exactly as vulnerable.
• Validate input as well, but as a second layer. Validation catches nonsense; parameterisation is what actually prevents injection.
• Apply least privilege to the application's database account. An application that only reads should not connect with an account that can drop tables — this is what limits the damage when something else fails.
• Do not expose database error messages to users. They are a map of your schema, and attackers read them.
• ⚠ Test your own code against it in an environment you own — and note the authorisation rule below: testing someone else's system without written permission is a criminal matter under federal and Florida law.

⚠⚠ SELECT before you DELETE — the habit that saves careers

• An UPDATE or DELETE without a WHERE clause modifies every row in the table, and databases execute it instantly and without asking.
• ⚠⚠ Write the WHERE clause first, run it as a SELECT, and look at what comes back. If the SELECT returns what you intend to change, convert it. This single habit prevents the most common catastrophic mistake in database work.
• Wrap it in an explicit transaction so you can roll back after checking the affected row count. An unexpected row count is the signal to roll back, not to shrug.
• ⚠ Check which server you are connected to before running anything. Running a correct statement against production instead of development is one of the commonest serious incidents in this field — colour-code your connections and make production unmistakable.
• Take a backup before a bulk change, or copy the affected rows into a temporary table first.
• Do not disable constraints to make an import work. The constraint is preventing exactly the corruption you are about to create.
• Understand that DDL is frequently not transactional in the way DML is — a dropped column may not be recoverable by rollback.
• ⚠ If you do make a mistake, say so immediately. A destructive change reported within a minute is usually recoverable from backups and log; the same change concealed for an hour frequently is not — and organisations forgive the first far more readily than the second.

⚠⚠ An untested backup is not a backup

• Backup jobs report success and produce media that cannot restore. Organisations discover this at the worst possible moment.
• ⚠ The only evidence a backup works is a completed restore. Test restores on a schedule, into a separate environment, and verify the data.
• Know your recovery point and recovery time objectives and whether your arrangements actually meet them — most do not, and nobody checks until it matters.
• Understand the recovery model and the transaction log. Full backups alone give you last night; log backups give you the last few minutes, and the difference is a day of data.
• ⚠⚠ Keep at least one copy offline or immutable. Ransomware deliberately encrypts or deletes the backups it can reach, and a backup on a share the compromised account can write to is not protection.
• Follow 3-2-1: three copies, two kinds of media, one off-site.
• Document the restore procedure so someone else can execute it under pressure, and keep it somewhere that survives the outage.
• Verify what is actually included. Backup scope drifts as systems change, and new databases are routinely missed.

⚠⚠ Real data carries real obligations — and students meet this first in a database course

• The moment a system holds data about people, it is subject to law and to duties that have nothing to do with whether the code works.
• ⚠⚠ Never copy production data into a development or test environment casually. It is one of the commonest sources of real breaches, it is frequently prohibited by policy, and a development database is almost never secured to the standard the production one is. Use synthetic or properly de-identified data.
• De-identification is harder than removing names. Combinations of ordinary fields — date of birth, postcode, sex — re-identify people surprisingly often.
• Collect only what you need and keep it only as long as you need it. Data you do not hold cannot be breached, and retention is a policy decision with legal consequences.
• ⚠ Access only what your task requires. Broad database access is normal for a developer and querying a colleague's or a public figure's record is still a dismissible act — audit logs attribute it to your account.
• Encrypt sensitive data in transit and at rest, and never store passwords recoverably — they are hashed with a purpose-built algorithm, never encrypted and never plain.
• ⚠ Sector rules stack on top: health data under HIPAA, student records under FERPA, card data under PCI DSS, and Florida's Digital Bill of Rights for businesses meeting its thresholds.
• Report a suspected breach immediately. Notification obligations carry deadlines, and concealment turns a manageable incident into a serious one.
• ⚠ Rule 11 applies — privacy law is moving quickly at state level; verify current requirements.

⚠⚠ Authorisation is the line between a computing professional and a defendant

• Never access, scan, modify, or extract data from a system you do not have permission to touch. The technical knowledge these courses give you is exactly what makes unauthorised access easy, which is why the rule matters more in this field than in most.
• Federal exposure: the Computer Fraud and Abuse Act criminalises unauthorised access to a protected computer, and "exceeding authorised access" has been read broadly.
• ⚠⚠ Florida exposure is separate and additional: the Florida Computer Crimes Act, Chapter 815, Florida Statutes, makes offences against computer users, systems and data punishable under state law independently of federal law.
• Having credentials is not the same as having authorisation. Being technically able to query a table or read a record does not mean you may, and systems log that you did.
• Use the lab and build your own. A local database, a virtual machine, or a personal cloud account costs little or nothing and lets you practise lawfully anything that would be unlawful elsewhere.
• Get scope in writing before any authorised testing or administrative engagement.
• ⚠ Rule 11 applies — computer crime and privacy law changes; verify rather than relying on a course guide.

How Florida course levels affect transfer

The first digit of an SCNS number denotes the year of offering, not transferability. Courses at the 1000 and 2000 levels transfer transparently between Florida public institutions, and 3000 to 4000 is unproblematic since both are upper division. The boundary that actually matters is 2000 to 3000, where lower-division credit generally cannot satisfy an upper-division requirement — and it is live here, since Daytona State offers both associate-level and bachelor of science coursework in these prefixes.

COP4709 is 3 credits and approximately 45 contact hours, offered spring only at Daytona State, with COP4708 as prerequisite.