Database Management/Database Design
This lesson introduces database design concepts.
Objectives and SkillsEdit
Objectives and skills for this lesson include:
- Understand logical database design concepts.
- Understand physical database design concepts.
- Apply logical and physical database design concepts.
Using an SQL-based DBMS (Microsoft SQL Server, MySQL, Oracle Database, PostgreSQL, SQLite, etc.), create a relational database for one or more of the following datasets. Include appropriate data types, primary and foreign keys, constraints, and default values where appropriate. Dump your completed database schema to an SQL script and review the results. If your DBMS supports ER diagrams, design the database using the ER diagram feature.
- A car dealership wants a database to track sales by customer, vehicle, and sales person.
- A college wants a database to track enrollment by student, course, and instructor.
- A company wants a database to track their organizational structure by department, employee, and location.
- A computer repair company wants a database to track repairs by customer, computer, and technician.
- A doctor's office wants a database to track patient visits by doctor, patient, and visit.
- A library wants a database to track books by title, author, and genre.
- A YouTube subscriber wants a database to track videos by subject, YouTuber, and playlist.
- Choose your own dataset similar to the above with at least three related entities.
- The process of database design includes:
- Analyze data requirements
- Determine data relationships and dependencies
- Create a logical design
- Normalize the logical design
- Create a physical design
- Test the physical design
- artificial key
- See surrogate key
- candidate key
- Any set of columns that have a unique combination of values in each row, with the additional constraint that removing any column would produce duplicate rows.
- The numerical relationship between rows of one table and rows in another.
- data dictionary
- A centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.
- A type of cardinality that refers to the relationship between two entities A and B in which A may contain a parent instance for which there are many children in B and vice versa.
- natural key
- A primary key based on real-world observables.
- A type of cardinality that refers to the relationship between two entities A and B in which one element of A may only be linked to one element of B, and vice versa.
- A type of cardinality that refers to the relationship between two entities A and B in which an element of A may be linked to many elements of B, but a member of B is linked to only one element of A.
- A representation, formal naming and definition of the categories, properties and relations between concepts, data and entities.
- The database structure described in a formal language supported by the database management system (DBMS).
- surrogate key
- an attribute created to function as a key and not used for identification outside the database.
- Database Models
- E-R Diagrams
- Database Development Process
- Medium: Create ER Diagram of a Database in MySQL Workbench
- Dataedo: How to create ER diagram for existing SQL Server database with SSMS
- McFadyen: Relational Databases and Microsoft Access - Chapter 7 (Entity Relationship Modeling)
- OERCommons.org: Database Development Life Cycle
- Watt: Database Design - 2nd Edition - Chapter 8 (Entity Relationship Data Model)
- Wikipedia: Database design
- Wikipedia: Candidate key
- Wikipedia: Cardinality (data modeling)
- Wikipedia: Data dictionary
- Wikipedia: Many-to-many (data model)
- Wikipedia: Primary key
- Wikipedia: One-to-one (data model)
- Wikipedia: One-to-many (data model)
- Wikipedia: Ontology (information science)
- Wikipedia: Database schema