Database Management/Database Design

From Wikiversity
Jump to navigation Jump to search

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]

  1. Wikipedia: Database design
  2. Wikibooks: Database Design/Database Development Process

Multimedia[edit | edit source]

  1. YouTube: How to Design Your First Database
  2. YouTube: Database Design Tutorial
  3. YouTube: Database Design
  4. YouTube: Entity Relationship Diagram (ERD) Training Video
  5. YouTube: Manage and Create Diagrams of Your SQL Database for Free with DBeaver
  6. YouTube: How to Make and Use an ERD/EER Diagram in MYSQL Workbench
  7. 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.

  1. A car dealership wants a database to track sales by customer, vehicle, and sales person.
  2. A college wants a database to track enrollment by student, course, and instructor.
  3. A company wants a database to track their organizational structure by department, employee, and location.
  4. A computer repair company wants a database to track repairs by customer, computer, and technician.
  5. A doctor's office wants a database to track patient visits by doctor, patient, and visit.
  6. A library wants a database to track books by title, author, and genre.
  7. A YouTube subscriber wants a database to track videos by subject, YouTuber, and playlist.
  8. 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:[1]
    • 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.[2]

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.[3]
cardinality
The numerical relationship between rows of one table and rows in another.[4]
data dictionary
A centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.[5]
many-to-many
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.[6]
natural key
A primary key based on real-world observables.[7]
one-to-one
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.[8]
one-to-many
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.[9]
ontology
A representation, formal naming and definition of the categories, properties and relations between concepts, data and entities.[10]
schema
The database structure described in a formal language supported by the database management system (DBMS).[11]
surrogate key
an attribute created to function as a key and not used for identification outside the database.

See Also[edit | edit source]

References[edit | edit source]