Relationships Between Database Tables
association between entities, e.g.
- students take courses
- courses contain students
Two entities are related by the degree
- a course contains one or more students
- an instructor is assigned one and only one department
Type of Relationship
one-to-one relationship
many-to-many relationship
zero-to-many relationship
one-to-many relationship
Good Design: One to Many Relationship
- Look at the relationship both ways
- Each and every lecturer is assigned to one and only one department
- Each and every department contains one or many lecturers
- This is a 1 to many (1:M) relationship (this is good!)
Bad Design: Many to Many Relationship
student to course
- student to registration one-to-many relationship
- course to registration one-to-many relationship
Database Design Summary
- identify and model entities (tables)
- identify and model attributes (columns)
- identify unique identifiers (for each table)
- identify and model relationships (between tables)
resolve any design issues
- use junction tables rather than Many-to-Many
- if you have 1 to 1, then do you need it?
- entities -> tables
- attributes -> columns
- unique identifiers -> primary keys
- relationships -> foreign keys
Database Design Implementation
- no spaces in names
- (SQL doesn’t like them!)
- keys – use INT type
- faster
- reduce errors, consistent format (10-digit phone number)
- can use CHAR for small/appropriate tables (e.g. CA,AL,AK)
- text
- VARCHAR(50) preferred
Table and column names
- must start with a letter
- followed by letters, numbers, or underscores
- not to exceed a total of 30 characters in length
- not SQL reserved keywords
- e.g. “select”, “create”, “insert”, etc.
Design a Database
your task is to create an Entity Relationship (ER) Diagram
- create entities, attributes and relationships
- create your ER diagram
- start with your tables / entities
- start with your column names / attributes
- underline primary keys
- relationships
- also consider
- data types (integer, date, varchar)
- any restraints (set amount >= 0)
- you are allowed to use AI to create test data
- create test data
- to see if your data types are correct
- save test data in excel