Relational Database

Philosophy

[Name of the Writer]

[Name of the Institution]

Part 1:

Conceptual Design:

A conceptual model is a representation of a system, made of the composition of concepts which are used to help people know, understand, or simulate a subject the model represents.

Entities:AdminProfessorStaffDepartmentFacultyCourseStudentRoomBuilding

Supertype:EmployeeSubtype:StaffAdminProfessor

Primary key:

Employees:id

Studentstudent

Coursecourse_id

Facultyfaculty

DepartmentDeptt_id

Candidate Key:

Primary keys are also used as a candidate key. There can be more than one candidate key. Candidate key is used to identify uniquely data from database

Surrogate key:

Room:

Room_id

Relationships and Cardinalities:

One to One relationship between Student and Department because one student has only one department.

One to many relationships between Student and Course because one student has enrolled in many courses.

One to one relationship between Building and Room because each room is associated with each building.

One to one relationship between faculty and Department because every faculty member has one department.

Many to Many relationships between Course and professor because one professor has taught many courses at a time.

One to many relationships between faculty and Student because one faculty member has advised many students at a time.

Conceptual ER Diagram:

Part 2:

Logical Design:

A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database. Features of a logical data model include: Includes all entities and relationships among them. All attributes for each entity are specified.

Describe Data Attributes and their types for each entity

Employee:

+Id: int (PK)+First_Name: Varchar+Last_Name: Varchar+Hire_date: Date

Admin

+Admin_position

Professor

+Rank: int +Specialty: Varchar

Staff:

+level: int

Student

+Stud_id: Varchar (PK) +First_Name: Varchar +Last_Name: Varchar +Email: Varchar

Department

+Deptt_id: int +Deptt_Name: Varchar

Faculty

+Faculty_id: int (PK)+Faculty_Name: Varchar

Room:

+Room_id: int+type: char

Identify Foreign keys for each identity:

In student class stud_id is the foreign key for courses class and the course_id in Courses class is the foreign key for student class.

In Professor class Emp_id is the foreign key for Faculty class and the Faculty_id in faculty class is the foreign key for professor class.

In Department class Deptt_id is the foreign key for Course class and the Course_id in Course class is the foreign key for Department class.

If there are any one to one relationship then describe:

One to One relationship between Student and Department because one student has only one department.

One to one relationship between Building and Room because each room is associated with each building.

One to one relationship between faculty and Department because every faculty member has one department.

Logical Model Diagram: