Applied Database I

Course Description

Applied Database I covers design and implementation of database systems within the concept of central administration and structured data storage. A programming project is required.

Within the SCNS taxonomy, COP is the Computer Programming prefix. Daytona State publishes this at 3 credits, offered fall and spring, with COP2800, COP2220 or COP2360 as prerequisite.

⚠ This course sits at a junction in the programme

COP4708 is the prerequisite for both COP4709 (Applied Database II, spring only) and CEN4801 (Systems Integration, fall only), so its timing determines the shape of two later terms. Plan the sequence with an advisor rather than discovering the constraint at registration.

Database design is the part of a system that is hardest to change afterwards. Application code can be rewritten in an afternoon; a data model with a structural flaw is embedded in every query, every report, every integration, and years of accumulated data — and organisations live with bad ones for decades because the cost of fixing them keeps rising. That asymmetry is why the design half of this course matters more than the implementation half.

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 database systems, their architecture, and central administration.
• Describe the relational model and its foundations.
• Model a problem domain using entity relationship modelling.
• Identify entities, attributes, relationships, and cardinality.
• Translate a conceptual model into a relational schema.
• Select and enforce primary keys appropriately.
• Define foreign keys and referential integrity.
• Apply normalisation through third normal form.
• Explain what each normal form prevents.
• Describe when and why denormalisation is deliberately chosen.
• Choose data types appropriately and describe their consequences.
• Implement constraints to enforce business rules in the database.
• Write SELECT queries including filtering, ordering, and aggregation.
• Write joins of all common types and predict their row counts.
• Write subqueries and describe alternatives to them.
• Write INSERT, UPDATE and DELETE statements safely.
• Use transactions and describe ACID properties.
• Describe concurrency, locking, and deadlock.
• Create and use indexes and describe their costs.
• Read a query execution plan at an introductory level.
• Diagnose and improve a slow query.
• Connect an application to a database securely.
• Prevent SQL injection using parameterised queries.
• Implement a working database-backed programming project.

Optional Outcomes

• Describe views and their uses.
• Describe stored procedures and functions.
• Describe database security and role-based permissions.
• Describe backup, recovery, and disaster planning.
• Describe non-relational databases and when they suit.
• Describe database administration as a career.

Major Topics

Required Topics

• Database systems and architecture
• The relational model
• Entity relationship modelling
• Entities, attributes, relationships, cardinality
• Conceptual to relational translation
• Primary keys
• Foreign keys and referential integrity
• Normalisation to third normal form
• What each normal form prevents
• Deliberate denormalisation
• Data types
• Constraints and business rules
• SELECT queries
• Joins and row counts
• Subqueries
• Safe data modification
• Transactions and ACID
• Concurrency, locking, deadlock
• Indexes and their cost
• Execution plans
• Query tuning
• Secure application connections
• Parameterised queries
• The programming project

Optional Topics

• Views
• Stored procedures and functions
• Security and permissions
• Backup and recovery
• Non-relational databases
• Database administration as a career

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.
• PostgreSQL, MySQL, SQLite and SQL Server Developer Edition — all free; install one and build something with real data rather than working only in the lab.
• Your database's own documentation — authoritative, version-correct, and far better than copied answers; SQL dialects differ in exactly the places that matter.
• OWASP (owasp.org) — free; its guidance on injection and secure data handling is the standard reference.

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

⚠⚠ Normalise until it hurts, denormalise until it works — and know which you are doing

• Normalisation exists to prevent update anomalies: the same fact stored in two places, which then disagree. Every normal form prevents a specific class of that, and knowing which is what distinguishes design from ritual.
• Third normal form is the working target for a transactional database, and most real designs should reach it before anyone considers deviating.
• ⚠ Denormalisation is a deliberate, measured trade — you accept redundancy to gain read performance, and you take on the obligation to keep the copies consistent. Denormalising because normalising was hard is not denormalisation, it is a bug.
• Choose keys carefully. A natural key that can change — an email address, a phone number, a national identifier — propagates that change through every referencing table.
• ⚠ Enforce integrity in the database, not only in the application. Applications get bypassed by imports, scripts, other applications and manual fixes — the constraint in the database is the only rule that is actually enforced.
• Model what is true, not what today's screen shows. A design shaped around a current user interface breaks the first time the interface changes.
• Ask about the exceptions early. "Every order has exactly one customer" is where you find out about the case that has two.
• Get the data types right the first time. Storing dates as text or money as floating point causes problems that outlive everyone involved.
• Name things consistently and document the model. The schema is read by people for as long as the system exists.

⚠ It was fast on a hundred rows — that tells you nothing

• Query performance problems are invisible on small data and appear all at once in production. Everything is fast on a hundred rows.
• Test with realistic volumes. Generate a million rows and see what happens — it takes ten minutes and it changes what you build.
• ⚠ Learn to read an execution plan. It tells you what the database is actually doing, and a full scan where you expected an index lookup is the single most useful thing to be able to spot.
• Index the columns you filter and join on, and understand that indexes are not free: they cost storage and they slow every insert and update. Indexing every column is a common beginner error.
• ⚠ A function applied to a column in a WHERE clause usually defeats the index on it — one of the most common causes of a query that should be fast and is not.
• Retrieve only the columns and rows you need. Selecting everything and filtering in the application moves the work to the wrong place and moves the data across the network.
• Watch for the query inside a loop. Executing one query per row of a result set is the classic performance disaster, and it is invisible in testing.
• Measure before and after. Optimisation without measurement is guessing, and the bottleneck is regularly not where you assumed.

⚠⚠ 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.

⚠⚠ 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.

COP4708 is 3 credits and approximately 45 contact hours, offered fall and spring at Daytona State.

⚠ It gates both COP4709 and CEN4801 — plan the sequence with an advisor.