Question

The Somerton High School PTO is creating a website that will allow students to quickly look up their homework assignments. The database supporting this website will keep track of departments, teachers, classes, and assignments. Each department has a name such as “Math”, “Science”, or “English” and each teacher is a member of a single department. For each teacher, the database should store a title (“Mr.”, “Ms.”, “Dr.”, etc), first name, middle initial, last name, office phone number, and e-mail address. Each class is taught by a one teacher; for each class, the database should store a name, description, meeting period (for example, seventh period), and room number. Finally, each class has multiple assignments. For each assignment, the database should store the date assigned, the date due, and a text description of the work assigned. Obviously, the database also needs to remember which class each assignment is for, and which teacher teaches each class.

A. Design a database to hold all this information. You may create synthetic keys as necessary or convenient.

B. Write outline notation and justify the primary key you have chosen for each table in the database.

C. In Access, create all the necessary tables for this database, and choose a reasonable datatype for each attribute. Assume phone numbers are in standard US format. If you use any “AutoNumber” synthetic primary keys, note that a matching foreign key should have a datatype of “Number”, with a field length of “Long integer”. Create all necessary relations between the tables and enforce referential integrity. You do not have to enter any data or create any forms. Create a “Documenter” report for the database.

Answer 1

Database outline and ER Diagram are as follows:

Following are the tables in the database :

1. DEPARTMENT
2. TEACHER
3. CLASS
4. ASSIGNMENT

We have following constraint type :

1. Primary key
2. Foreign key
3. Check condition
4. NOT NULL
5. UNIQUE

Attributes(Columns in table), Data Type & Constraints for the tables are given below:

Table : DEPARTMENT

Column_name	Data_type	Contraint_type
DEPT_ID	INT	PRIMARY KEY
DEPT_NAME	VARCHAR(50)	NOT NULL

Table : TEACHER

Column_name	Data_type	Contraint_type
TEACHER_ID	INT	PRIMARY KEY
TITLE	VARCHAR(50)	NOT NULL
FNAME	VARCHAR(50)	NOT NULL
MINIT	VARCHAR(50)	NOT NULL
LNAME	VARCHAR(50)	NOT NULL
OFFICE_PHONE	VARCHAR(50)	NOT NULL
EMAIL	VARCHAR(50)	NOT NULL, UNIQUE
DEPT_ID	INT	FOREIGN KEY references DEPARTMENT table

Table : CLASS

Column_name	Data_type	Contraint_type
CLASS_ID	INT	PRIMARY KEY
NAME	VARCHAR(50)	NOT NULL
DESCRIPTION	VARCHAR(50)	NOT NULL
MEETING_PERIOD	VARCHAR(50)	NOT NULL
ROOM_NO	INT	NOT NULL
TEACHER_ID	INT	FOREIGN KEY references TEACHER table

Table : ASSIGNMENT

Column_name	Data_type	Contraint_type
ASSIGNMENT_ID	INT	PRIMARY KEY
DATE_ASSIGNED	DATE	NOT NULL
DATE_DUE	DATE	NOT NULL
DESCRIPTION	VARCHAR(50)	NOT NULL
CLASS_ID	INT	FOREIGN KEY references CLASS table

ER DIAGRAM :