University of Chicago Database Design by Using SQL Tool Project

Case:

An employee can work on one and only one project but a project can be done by one or many employees. Projects are tracked by project ID, project name, duration (number of days), and completion date. We track employees by employee ID, their first name, last name, and date of birth (DOB). We have 2 employees who are working on 2 projects as follows:

Project:

• Project_id= 10001, project_name=outsourcing, duration=120 days, completion_date=1/25/2017
• Project_id= 10002, project_name=housing, duration=255 days, completion_date=11/01/2018

Employee:

• Emp_id=22001, Sam Adams, DOB=1/13/1964 (works on the housing project)
• Emp_id=22002, Rachel Williams, DOB=10/15/1999 (works on the outsourcing project)

—

NOTE: in order to make changes in the tables, if needed, we need to make a quick change in the Workbench DBMS. Go to Edit > Preferences. Click on “SQL Editor” and on the right at the very bottom, uncheck “Safe Updates”. Next, reconnect to the database (click on the icon that says “Reconnect to DBMS”).

Step 1. Identify entities, their attributes, and their relationships.

Step 2. Diagram entities (attributes included) and their relationships (with min and max on both sides) using crow’s foot notation.

Step 3. Create a database, tables, and add values in the tables using SQL.

CREATE DATABASE AND TABLES.

• Create a database and name it your UscID (if you have already created the database named with your Usc ID, skip this step).
• Create tables for project and employee in the database named with your Usc ID.
• Add 2 records in the project table and 2 records in the employee table.

Step 2. Diagram entities (attributes included) and their relationships using crow’s foot notation.

Step 3. Create a database, tables, and add values in the tables using SQL (3 points).

CREATE DATABASE AND TABLES.

• Create a database and name it your USF ID (if you have already created the database named with your USF ID, skip this step).
• Create tables for project and employee in the database named with your USF ID.

create database ldu5;

Project table:

create table project
(project_id int primary key not null,
project_name varchar(250),
duration int,
completion_date date);

Employee table:

create table employee
(emp_id int primary key not null,
first_name varchar(250),
last_name varchar(250),
dob date,
project_id int,

foreign key (project_id) references project(project_id));