COSC 341 Midterm Exam Part B NAME: _________________________

Spring 2023

The following ER Diagram represents the data model for a maritime system that will track the movements of

ships visiting ports and their home ports.

(1) Create a relational schema (listing of table structure with attributes that includes specification of keys

and lines+arrows addressing foreign keys) for this data model[15 points]

(2) Write the create table statements in SQL with proper syntax (make assumptions about data types,

make sure to include keys/foreign keys, think carefully about how to handle the weak entity and the

many-to-many relationship) [20 points]

SHIP

PORT

Home

Port

Visits

es

Country Name

Ship Name Owner Tonnage

Port Name

Arrive Date

Depart Date

Tonnage

N

M

(3) (10 pts) Provide a definition or answer:

(A) From a database perspective, data can be classified into two types: atomic and compound.

a. Define atomic

b. Define compound

(B) If a relation A has 4 attributes (hence degree 4) and 7 tuples, and relation B has 3 attributes and 5

tuples what is the degree and size of A X B?

(C) Define Primary Key:

(D) Define Foreign Key:

(4) (10 points) For the relation:

(5) (10 pts) Describe at least 3 different type of JOINs and how they differ.

A B C

a1 b1 c1

a1 b2 c1

a1 b2 c2

a1 b3 c3

a1 b3 c4

a2 b4 c5

a2 b5 c5

What is a valid (composite, candidate) key?

Explain in writing why it is a valid key and why other choices are not:

What functional dependencies (between A, B, C) are implied in rows:

(6) (15 pts)

Given relation Table1(A,B,C):

And relation Table2(A,B,C):

4 (A) Provide the relation for Table1 ∩ Table2:

4 (B) Provide the relation for Table1 – Table2:

4 (C) Compute (Table1 – Table2)  (Table2 – Table1), often called the "symmetric difference"

of Table1 and Table2.

A B C

1 2 3

4 2 3

4 5 6

2 5 3

1 2 6

A B C

2 5 3

2 5 4

4 5 6

1 2 3

5) (8 pts) Based on the tables below, provide the relation for TableB ÷ TableA:

TableA

PNO Desc

8 Rachet & Socket

10 Hammer

14 Screwdriver

TableB

Emp PNO Desc

John 8 Rachet & Socket

John 10 Hammer

Bill 14 Screwdriver

Mary 10 Hammer

Mary 14 Screwdriver

Bill 8 Rachet & Socket

John 14 Screwdriver

Mary 12 Saw

6) (12 pts) Write a series of relational algebra statements that based on the relations in the pizza SQL database we looked at that will implement:

“Find the names of all people who eat at least one pizza served by Little Caesars but who do

not frequent Little Caesars.”

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.