a.
A cruise company, such as carnival cruise lines, maintains a fleet of ships each of which
has associated with it a name, year of construction, length, height, tonnage, port of
registry, and passenger capacity. The cruise company offers vacationers cruises, each of
which is on a particular ship and has a date of departure and itinerary. As part of the
itinerary, the ship visits ports At each port of call, there are optional short excursions
that the passengers can purchase. Construct an E-R diagram for a cruise company.
b.) A company is organized into a number of departments. Employees are assigned to departments
within the company. Each employee (except for perhaps the company president ) reports to a
supervisor. The company pursues a number of projects, each projects controlled by a particular
department. Employees within the company are assigned to work on projects.
c. Colleges offer a variety of majors, from which students are required to choose.(some students choose
double majors). Each major is associated with a department within the college.(some majors, such as
biochemistry, are associated with more than one department.) Each major requires students to
complete certain required coursed, but also allows them to take courses chosen from among a set of
electives. Some courses have prerequisites that must be completed before that course can be take.
Professors are assigned to departments within the college.
2. Deriver tables from each of the E-R diagrams shown in question 1
a. A cruise company, such as carnival cruise lines, maintains a fleet of ships each of which
has associated with it a name, year of construction, length, height, tonnage, port of
registry, and passenger capacity. The cruise company offers vacationers cruises, each of
which is on a particular ship and has a date of departure and itinerary. As part of the
itinerary, the ship visits ports At each port of call, there are optional short excursions
that the passengers can purchase. Construct an E-R diagram for a cruise company.
Table – Cruise_Company
Attribute Name Type
Name(Primary Key) Varchar(20)
Address Varchar(20)
PhoneNumber Int
Table - Ship
Attribute Name Type
Name Varchar(20)
Capacity Varchar(20)
Length Int
Height Int
YearofConstruction Int
PortofRegistry Varchar(20)
ShipID(primary key) Varchar(20)
Table – Cruise
Attribute Name Type
CID (primary key) Int
Date Varchar(20)
Itinerary Varchar(20)
Table – Port
Attribute Name Type
PID(primary key) Int
Name Varchar(20)
Location Varchar(20)
Table – Excursion
Attribute Name Type
Name Varchar(20)
Location Varchar(20)
Table – Passenger
Attribute Name Type
Name Varchar(20)
PassportNumber(primary key) Int
b.) A company is organized into a number of departments. Employees are assigned to departments
within the company. Each employee (except for perhaps the company president ) reports to a
supervisor. The company pursues a number of projects, each projects controlled by a particular
department. Employees within the company are assigned to work on projects.
Table -Employee
Attribute Name Type
EID(Primary key) int
Name Varchar(20)
FirstName Varchar(20)
MiddleName Varchar(20)
LastName Varchar(20)
Address Varchar(20)
Table - Department
Attribute Name Type
Name(primary key) Varchar(20)
Locations Varchar(20)
Dept_Number(primary key) int
Table – Project
Attribute Name Type
Name(primary key) Varchar(20)
Project_Number(primary key) int
Location Varchar(20)
Table - Supervisor
Attribute Name Type
Supervisor_ID(primarykey) int
Name Varchar(20)
c. Colleges offer a variety of majors, from which students are required to choose.(some students choose
double majors). Each major is associated with a department within the college.(some majors, such as
biochemistry, are associated with more than one department.) Each major requires students to
complete certain required coursed, but also allows them to take courses chosen from among a set of
electives. Some courses have prerequisites that must be completed before that course can be take.
Professors are assigned to departments within the college.
Table - College
Attribute Name Type
CID ( Primary key) int
Name Varchar(20)
Table - Department
Attribute Name Type
DeptID(Primary Key) Int
Name(primary Key) Varchar(20)
Location Varchar(20)
Table - Professor
Attribute Name Type
PID(primary key) Int
Name Varchar(20)
Table - Student
Attribute Name Type
SID(primary key) Int
Name Varchar(20)
FName Varchar(20)
MName Varchar(20)
LName Varchar(20)
Address Varchar(20)
Table - Course
Attribute Name Type
CourseID(primary key) Int
Name Varchar(20)
Table - Major
Attribut Name Type
MajorID Int
Name Varchar(20)
3. Page 315 exercise 7.2
a) Construct an E-R diagram that models exams as entities and uses a ternary relationship.
b.) Construct an alternative E-R diagram that uses only a binary relationship between student and
section. Make sure that only one relationship exists between a particular student and selection pair, yet
you can represent the marks that a student gets In different exams.