4. Examples
For each of the following examples, the goal is to determine:
- What data and data types do we need to store?
- What are the relationships in the database?
- What could our tables look like? What are the primary and foreign keys?
Example 1: Employees and Departments
A company has several employees and departments. Each employee has a number and name, and each works in a specific department. Each department has a name, location and each department can have multiple employees.
Solution
Step 1: What data and data types do we have?
- Employee Number - integer
- Employee Name - String
- Employee Department - String
- Department Name - String
- Department Location - String
Step 2: What are the relationships between employees and departments?
- 1:M, one department with many employees
Step 3: What do the tables for departments and employees look like? What are the primary and foreign keys?
Departments department_name (Primary Key) (String) department_location (String) Employees employee_number (Primary Key) (Integer employee_name (String) employee_department (Foreign Key - Departments.department_name) (String)
Example 2: Students and Courses
A university offers various courses, and students enroll in these courses. Each student has a name, an id, and can enroll in multiple courses. Each course has a name, id and can have multiple students.
Solution
Step 1: What data and data types do we have?
- Student ID - Integer
- Student Name - String
- Course ID - Integer
- Course Name - String
Step 2: What are the relationships between students and courses?
- M:M, many students can take many courses
Step 3: What do the tables for students and courses look like? What are the primary and foreign keys?
Students student_id (Primary Key) (Integer) student_name (String) Courses course_id (Primary Key) (Integer) course_name (String) Student_Course student_id (Foreign Key - Students.student_id) (Integer) course_id (Foreign Key - Courses.course_id) (Integer)
Example 3: Authors and Books
In a library, each book has an ID, a title and is written by a specific author. Each author has a name but one author may write multiple books.
Solution
Step 1: What data and data types do we have?
- Book ID - Integer
- Book Title - String
- Author Name - String
Step 2: What are the relationships between books and authors?
- 1:M, one author writes many books
Step 3: What do the tables for books and authors look like? What are the primary and foreign keys?
Authors author_id (Primary Key) (Integer) author_name (String) Books book_id (Primary Key) (Integer) book_title (String) author_id (Foreign Key - Authors.author_id) (Integer)
Example 4: Students and Advisors
Each student at a university has a name, an ID, and is assigned to an advisor, but an advisor (who just has a name) can oversee multiple students.
Solution
Step 1: What data and data types do we have?
- Student ID - Integer
- Student Name - String
- Advisor Name - String
Step 2: What are the relationships between students and advisors?
- 1:M, one advisor has many students
Step 3: What do the tables for students and advisors look like? What are the primary and foreign keys?
Advisors advisor_id (Primary Key) (Integer) advisor_name (String) Students student_id (Primary Key) (Integer) student_name (String) advisor_id (Foreign Key - Advisors.advisor_id) (Integer)
Example 5: Products and Categories
A store sells products, each product has a name, size price and id. Each product is categorized and can belong to one category. Each category (which can have multiple products) has a name.
Solution
Step 1: What data and data types do we have?
- Product ID - Integer
- Product Name - String
- Product Size - String
- Product Price - Decimal
- Category ID - Integer
- Category Name - String
Step 2: What are the relationships between products and categories?
- 1:M, one category has many products
Step 3: What do the tables for products and categories look like? What are the primary and foreign keys?
Categories category_id (Primary Key) (Integer) category_name (String) Products product_id (Primary Key) (Integer) product_name (String) product_size (String) product_price (Decimal) category_id (Foreign Key - Categories.category_id) (Integer)
Example 6: Patients and Doctors
In a hospital, each patient has an ID, blood type and name. Each patient is treated by one or more doctors. Each doctor, who has a name, salary and ID, can treat multiple patients.
Solution
Step 1: What data and data types do we have?
- Patient ID - Integer
- Patient Blood Type - String
- Patient Name - String
- Doctor ID - Integer
- Doctor Name - String
- Doctor Salary - Decimal
Step 2: What are the relationships between patients and doctors?
- M:M, many patients can be treated by many doctors
Step 3: What do the tables for patients and doctors look like? What are the primary and foreign keys?
Patients patient_id (Primary Key) (Integer) patient_name (String) patient_blood_type (String) Doctors doctor_id (Primary Key) (Integer) doctor_name (String) doctor_salary (Decimal) Patient_Doctor patient_id (Foreign Key - Patients.patient_id) (Integer) doctor_id (Foreign Key - Doctors.doctor_id) (Integer)
Example 7: Courses and Instructors
Each course at a university, has an ID and a name, and is taught by one or more instructors. Each instructor (who also have an ID and a name) can teach multiple courses.
Solution
Step 1: What data and data types do we have?
- Course ID - Integer
- Course Name - String
- Instructor ID - Integer
- Instructor Name - String
Step 2: What are the relationships between courses and instructors?
- M:M, many courses can be taught by many instructors
Step 3: What do the tables for courses and instructors look like? What are the primary and foreign keys?
Courses course_id (Primary Key) (Integer) course_name (String) Instructors instructor_id (Primary Key) (Integer) instructor_name (String) Course_Instructor course_id (Foreign Key - Courses.course_id) (Integer) instructor_id (Foreign Key - Instructors.instructor_id) (Integer)