Database Management/Database Design
This lesson introduces database design concepts.
Objectives and Skills[edit | edit source]
Objectives and skills for this lesson include:
- Understand logical database design concepts.
- Understand physical database design concepts.
- Apply logical and physical database design concepts.
Readings[edit | edit source]
Multimedia[edit | edit source]
- YouTube: How to Design Your First Database
- YouTube: Database Design Tutorial
- YouTube: Database Design
- YouTube: Entity Relationship Diagram (ERD) Training Video
- YouTube: Manage and Create Diagrams of Your SQL Database for Free with DBeaver
- YouTube: How to Make and Use an ERD/EER Diagram in MYSQL Workbench
- YouTube: How to Create a Database Diagram Using SQL Server Management Studio
Activities[edit | edit source]
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.
Lesson Summary[edit | edit source]
- 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
- Entity-Relationship (ER) diagrams may be used with the logical and/or physical design of a database.
Key Terms[edit | edit source]
- 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.
See Also[edit | edit source]
- Database Models
- E-R Diagrams
- Database Development Process
- 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)
References[edit | edit source]
- Wikipedia: Database design
- 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