The Engineering School Conceptual Design

NB: We do not resell your papers. Upon ordering, we do an original paper exclusively for you.

NB: All your data is kept safe from the public.

Table of Contents

Entity Relationship Diagram for the Engineering School
Phase II
Relationships
Phase III
Structured Query Language (SQL) Statements

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.

Figure 1

Phase II

Figure 2 below shows a logical model for the Engineering school Design:

Figure 2

Relationships

Figure 3 below shows the database object definition:

Figure 3

Figure 4 below shows the database schema format:

Figure 4

Phase III

Figure 4 above shows school Engineering Database tables in 3^rd 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”;

Click Here To Order Now!