Member: The attributes of the member entity are: (SSN (identifier), Name, Address, Birth_Date, and Phone). The Name attribute is finally split to FirstName and LastName while Address attribute is a composite attribute composed of Street_Number, Apart_Number, City, Street and Zip_Code
Assumption: It is assumed that this entities attributes are common to every member of the school and hence shared by every member of the engineering school. The SSN attribute uniquely identifies a member.
FacultyRole: This entity has the following attributes: (Date_Joined, SSN (Identifier), FacultyRole_ID, Rank_ID). The attribute Date_Attended has a constraint (should be less than Feb 01, 2006).
Assumption: The SSN and Rank_ID attribute are foreign to the FacultyRole and primary to the Member Entity. FacultyRole_ID uniquely identifies FacultyRole entity.
StaffRole: This entity has the following attributes: (StaffRole_ID, SSN (Identifier), Contract_Years)
Assumption: The SSN attribute is foreign to the FacultyRole and primary to the Member Entity. StudentRole_ID uniquely identifies FacultyRole entity.
StudentRole: This entity has the following attributes: (StudentRole_ID, SSN (Identifier), Student_ID, Start_Date). The attribute Start_Date has a constraint (Should be later than Nov 01, 2001)
Assumption: The SSN attribute is foreign to the StudentRole and primary to the Member Entity. StudentRole_ID uniquely identifies StudentRole entity.
AssistantRole: This entity has the following attributes: (AssistantRole_ID, SSN (Identifier), Skills)
Assumption: The SSN attribute is foreign to the AssistantRole and primary to the Member Entity. AssistantRole_ID uniquely identifies an AssistantRole entity.
Department: This entity has the following attributes: (Dept_ID, Dept_Name and Dept_Location, Chair, Dean)
Assumption: Dept_ID uniquely identifies Department entity.
Course: This entity has the following attributes: (Course_ID, Dept_ID, FacultyRole_ID, Date_Held and Room_No)
Assumption: Dept_ID and FacultyRole_ID are foreign attributes to the Course entity. Course_ID uniquely identifies a Course entity.
Score: This entity has the following attributes: (Score_ID, StudentRole_ID, Course_ID, Grade, and Remark)
Assumption: StudentRole_ID and Course_ID are foreign attributes to Score entity. Score_ID uniquely identifies a score entity.
Role: This entity has the following attribute: (Role_Name).
Assumption: Role_Name attribute uniquely identifies a Role entity.
Rank: This entity has the following attributes: (Rank_ID, Role_Name, Rank_Name, Requirements)
Assumption: Role_Name is a foreign attribute to Rank entity. Rank_ID uniquely identifies a Rank entity.
Entity Relationship Diagram for the Engineering School
Figure 1 below shows a conceptual model for the Engineering School Design.
Phase II
Figure 2 below shows a logical model for the Engineering school Design:
Relationships
Figure 3 below shows the database object definition:
Figure 4 below shows the database schema format:
Phase III
Figure 4 above shows school Engineering Database tables in 3rd Normal form. The tables are well named and the attributes well defined.
Structured Query Language (SQL) Statements
Create database
CREATE DATABASE Engineering_School_DB
Create table
CREATE TABLE Member_tbl
(
SSN Autonumber(Random),
Dept_ID longinteger,
FirstName text(32),
LastName text(32)
Birth_Date datetime(small date),
Phone text(15),
Street_Number text(15),
Apart_Number text(15),
City text(32),
Zip_Code text(15)
)
CREATE TABLE AssistantRole_tbl
(
AssistantRole_ID Autonumber(Random),
SSN longinteger,
Skills text(50)
)
CREATE TABLE StaffRole_tbl
(
StaffRole_ID Autonumber(Random),
SSN longinteger,
Contract_Years byte
)
CREATE TABLE StudentRole_tbl
(
StudentRole_ID Autonumber(Random),
SSN longinteger,
Student_ID text(15),
Start_Date datetime(short date)
)
CREATE TABLE FacultyRole_tbl
(
FacultyRole_ID Autonumber(Random),
SSN longinteger,
Rank_ID longinteger,
Date_Joined datetime(Short date)
)
CREATE TABLE Score_tbl
(
Score_ID Autonumber(Random),
StudentRole_ID longinteger,
Course_ID long integer,
Score byte,
Remark text(50)
)
CREATE TABLE Course_tbl
(
Course_ID Autonumber(Random),
Dept_ID longinteger,
SSN long integer,
FacultyRole_ID longinteger,
Date_Held datetime(short date),
Room_No text(15)
)
CREATE TABLE Department_tbl
(
Dept_ID Autonumber(Random),
Dept_Name text(32),
Dept_Location text(32),
Chair longinteger,
Dean longinteger
)
CREATE TABLE Role_tbl
(
Role_Name text(15)
)
CREATE TABLE Rank_tbl
(
Rank_ID Autonumber(Random),
Role_Name text(15),
FacultyRole_ID longinteger,
Rank_Name text(32),
Requirements text(100)
)
- CREATE VIEW Staff-Hired
(
FirstName, LastName, Date_Joined
AS SELECT Member_tbl.FirstName, Member_tbl.LastName, FacultyRole_tbl.Date_Joined FROM Member_tbl, FacultyRole_tbl WHERE Member_tbl.SSN = FacultyRole_tbl.SSN;
)
- CREATE VIEW Department-Chair
(
FirstName, Dept_Name
AS SELECT Member_tbl. FirstName, Department_tbl. Dept_Name FROM Member_tbl, Department_tbl
WHERE Member_tbl.SSN = FacultyRole_tbl.SSN and FacultyRole_tbl.Rank_Name = “CHAIR”;
)
- CREATE VIEW Good-Student
(
Student_ID,FirstName, LastName
AS SELECT StudentRole_tbl.Student_ID, Member_tbl.FirstName, Member_tbl. LastName FROM Member_tbl, StudentRole_tbl, Score_tbl
WHERE Member_tbl.SSN = StudentRole_tbl.SSN and StudentRole_tbl.Student_ID = Score_tbl.Student_ID and Score_tbl.Score >= “50”;
WHERE Rank_Name = “CHAIR”;