Database Management/Database Design

This lesson introduces database design concepts.

Objectives and Skills

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

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

Multimedia

1. YouTube: How to Design Your First Database
2. YouTube: Database Design Tutorial
3. YouTube: Database Design

Activities

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

• 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

Key Terms

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.

cardinality

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.

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.

natural key

A primary key based on real-world observables.

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.

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.

ontology

A representation, formal naming and definition of the categories, properties and relations between concepts, data and entities.

schema

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

• 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)

References

← E-R Diagrams

Database Management

Database Functions →