The Engineering School Conceptual Design Coursework

Exclusively available on IvyPanda Available only on IvyPanda
Updated: Mar 12th, 2024

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

We will write a custom essay on your topic a custom Coursework on The Engineering School Conceptual Design
808 writers online

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)

1 hour!
The minimum time our certified writers need to deliver a 100% original paper

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.

Remember! This is just a sample
You can get your custom paper by one of our expert writers

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
Figure 1

Phase II

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

We will write
a custom essay
specifically for you
Get your first paper with
15% OFF
Figure 2
Figure 2

Relationships

Relationships

Relationships

Relationships

Figure 3 below shows the database object definition:

 the database object definition
 the database object definition
Figure 3

Figure 4 below shows the database schema format:

he database schema format
Figure 4

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”;

Print
Need an custom research paper on The Engineering School Conceptual Design written from scratch by a professional specifically for you?
808 writers online
Cite This paper
Select a referencing style:

Reference

IvyPanda. (2024, March 12). The Engineering School Conceptual Design. https://ivypanda.com/essays/the-engineering-school-conceptual-design/

Work Cited

"The Engineering School Conceptual Design." IvyPanda, 12 Mar. 2024, ivypanda.com/essays/the-engineering-school-conceptual-design/.

References

IvyPanda. (2024) 'The Engineering School Conceptual Design'. 12 March.

References

IvyPanda. 2024. "The Engineering School Conceptual Design." March 12, 2024. https://ivypanda.com/essays/the-engineering-school-conceptual-design/.

1. IvyPanda. "The Engineering School Conceptual Design." March 12, 2024. https://ivypanda.com/essays/the-engineering-school-conceptual-design/.


Bibliography


IvyPanda. "The Engineering School Conceptual Design." March 12, 2024. https://ivypanda.com/essays/the-engineering-school-conceptual-design/.

Powered by CiteTotal, easy reference maker
If you are the copyright owner of this paper and no longer wish to have your work published on IvyPanda. Request the removal
More related papers
Cite
Print
1 / 1