CSC 443 Data Base Management Systems

Dr. R. M. Siegfried

Assignment 6 - Creating the Database in Lecture #6

Due Monday, October 14, 2013

In lecture #6, we learned how to write SQL (Structure Query Language) in order to create a database structure and then to populate it (add its initial data to it).

Create your own copy of the database and then use a SELECT to display it on the screen and then print it out.

Database Table Structures

Employee

Field Name	Data Type	Constraint	Key
fname	varchar(15)	not null,
Minit	char
Lname	varchar(15)	not null
ssn	char(9)	not null	Primary
Bdate	date
Address	varchar(30)
Sex	char,
Salary	decimal(10,2)
Super_ssn	char(9)		Foreign (Ssn in Employee)
Dno	int	not null	Foreign (Dnumber in Department)

Department

Field Name	Data Type	Constraint	Key
Dname	varchar(15)	not null, unique
Dnumber	int	not null	Primary
Mgr_ssn	char(9)	not null	Foreign (Ssn in Employee)
Mgr_start_date		date

Dept_Locations

Field Name	Data Type	Constraint	Key
Dnumber	int	not null	primary, foreign (Dnumber in Department)
Dlocation	varchar(15)	not null	primary

Project

Field Name	Data Type	Constraint	Key
Pname	varchar(15)	not null, unique
Pnumber	int	not null	Primary
Plocation	varchar(15),
Dnum	int	not null	Foreign (Dnumber in Department)

Works_On

Field Name	Data Type	Constraint	Key
Essn	char(9)	not null	Primary, Foreign (Ssn in Employee)
Pno	int	not null	Primary, Foreign (Pnumber in Project)
Hours	decimal(3,1)	not null

Dependent

Field Name	Data Type	Constraint	Key
Essn	char(9)	not null	Primary
Dependent_name	varchar(15)	not null	Primary
Sex	char
Bdate	date
Relationship	varchar(8)

The Tables' Data