CSCI3901-Lab 9 Database Design Solved

Objective
In this lab, you will practice designing databases with key constraints.

Preparation
•    Make sure that you are using your own private database on db.cs.dal.ca.

–    To do so, execute the command use USERID; where USERID is your CSID.

•    Ensure that MySQLWorkbench is allowing you to do updates and deletions.

–    To do this, you may execute the command set SQL SAFE UPDATES=0;

–    Alternatively, you can go into the MySQLWorkbench Preferences, then the SQL Editor tab and uncheck the Safe Updates box.

Resources

•    MySQLWorkbench

Procedure
Set-up
1. Create two tables in your database and include some basic information into the tables using the following commands:

create table web ( web_id int primary key, URL varchar(50)

);

create table course ( course_key int primary key, name varchar(10), web_id int );

insert into web values

(1, "cs.dal.ca"),

(2, "google.com"),

(3, "dal.ca"); insert into course values

(1, "csci3901", 1),

(2, "csci5100", 1),

(3, "math1000", 3);

Lab steps
In part 1 you will execute a sequence of steps to modify the database you just created. In part 2, you will design a database given an ER diagram.

Part 1 - Key constraints

1.   Make the following changes to the database and report on their success or failure:

(a)   Add an entry to course with web id of NULL

(b)    Add an entry to course with web id of 2

(c)    Add an entry to course with web id of 4

(d)   Add an entry to web with web id of 5

2.   Explain how you could identify all the entries in course with bad web id keys.

3.   A foreign key constraint can be added to the course table with the command alter table course add foreign key (web_id) references web (web_id);

Determine whether the foreign key constraint can be successfully added with the above command in each of the following cases:

(a)   course contains a webid that is a bad foreign key

(b)   course contains a web id that is NULL

(c)    course contains only web ids that are in web

4.   Add web id as a foreign key in course, making any changes to either table necessary to do so.

5.   Explain what it means if you allow a foreign key column to be NULL

6.   Explain what it means if you do not allow a foreign key column to be NULL

7.   Make the following changes to the database and report on their success or failure:

(a)   Delete course key 2 in course

(b)   Delete web id 3 in web

(c)    Delete web (i.e. using drop table web;) (d) Delete course (i.e. using drop table course;)

8.   Explain why the previous commands succeeded or failed.

 

Figure 1: Entity relationship diagram

Part 2 - Database design Translate the ERD from fig. 1 to a set of tables in your database. Use the prefix ds   (for “dining service”) in your table names to let you group all the tables from this exercise.

In the figure, underlined attributes represent primary keys and attributes in curly braces {} represent multi-valued fields.

Questions
1.   How can foreign key constraints help to maintain the integrity of data in your database?

2.   Is there only one valid design for a database with a given ER diagram?

Reporting
1.   In one file, list

•    The members of your team.

•    Your answers to the questions in Part 1

•    The SQL commands to create your database in Part 2

•    Your answers to the broadening questions

2.   Generate a PDF from the document.