Database Design Exercise

DigHT 310
Database Design Exercise

Create a database schema for keeping track of a set of data of your choice. You could, for example, create a database for your DVD or CD collection, a club or church group membership list, or a fictional class list or company. The following requirements apply:

The database should consist of two or more tables.
Each table should have a data field for a unique numeric identifier for each record.
There should be at least one field in one of the tables that refers to another table.
Provide a short description of what each data field is for.
Each table should contain 7 to 10 records.

Here is a sample database schema, based on the fictitious business example we used in class. Note that this is the schema only; you should also make up some records for your tables.

Database: business_example

Table 1: employee
FIELD	DESCRIPTION
person_id	Unique numeric identifier for each employee
fname	Employee's first name
lname	Employee's last name
office	Employee's office number
phone	Employee's phone number
dept	Which department the employee works in

Table 2: department
FIELD	DESCRIPTION
dept_id	Unique numeric identifier for each dept
name	Department name
office	Department main office number
phone	Department main phone number
supervisor	ID of supervisor (person_id from employee table)

Table 3: project
FIELD	DESCRIPTION
proj_id	Unique numeric identifier for each project
client	Short name of client
lead	Employee in charge of each project (person_id from employee table)
phone	Client contact phone number

Here's an example of what one of the tables might look like with a few records in it:

person_id	fname	lname	phone	office	dept
1	Joe	Jones	123-4567	Room 119	2
2	Jane	Doe	234-5678	Room 128	2
3	Mike	Nelson	345-6789	Room 127	1
4	René	Johnson	456-7890	Room 210	3

Back BYU LiveCode Lessons Gateway

DigHT 310 Database Design Exercise

Database: business_example

DigHT 310
Database Design Exercise