CS 4347 — Database Systems, Homework 3

Download PDF

Translate the following ER Diagram into a relational database schema.

Consider the following EER diagram for a car dealer database; map this diagram into a relational database schema

Given the following relational database schema of a Library, Draw an ER Model that would be mapped into this schema

Assume the following relational schema and tables

Vehicle

VRNMaMoColor
123HondaHawkRed
234MazdaRX7Blue
345FordTaurusBlue
456FordRangerGreen
890SuzukiIntruderBlack

Own

VRNSSN
123bcd
234abc
456def
567ghi

Person

SSNNameAddrPhone
abcDaveBirchxxx
bcdMaryGroveyyy
cdeSriramOakzzz
defFangBirchwww
hijGilfrdBirchttt

Write a relational algebra query to return all tuples of Vehicle relation with Ma = ‘Honda’

  1. (Vehicle)

    • (123, ‘Honda’, ‘Hawk’, ‘Red’)

Write a relational algebra query to return the VRN and Color of Vehicles with Ma = ‘Honda’

  1. (Vehicle))

    • (123, ‘Red’)

Write the result of the query: Vehicle x Own (Cartesian Product)

VRNMaMoColorVRNSSN
123HondaHawkRed123bcd
123HondaHawkRed234abc
123HondaHawkRed456def
123HondaHawkRed567ghi
234MazdaRX7Blue123bcd
234MazdaRX7Blue234abc
234MazdaRX7Blue456def
234MazdaRX7Blue567ghi
345FordTaurusBlue123bcd
345FordTaurusBlue234abc
345FordTaurusBlue456def
345FordTaurusBlue567ghi
456FordRangerGreen123bcd
456FordRangerGreen234abc
456FordRangerGreen456def
456FordRangerGreen567ghi
890SuzukiIntruderBlack123bcd
890SuzukiIntruderBlack234abc
890SuzukiIntruderBlack456def
890SuzukiIntruderBlack567ghi

Write the result of the query: Vehicle Own (Natural Join)

VRNMaMoColorVRNSSN
123HondaHawkRed123bcd
234MazdaRX7Blue234abc
456FordRangerGreen456def

Write the result of the query: Own x Person (Cartesian Product)

VRNSSNSSNNameAddPhone
123bcdabcDaveBirchxxx
234abcabcDaveBirchxxx
456defabcDaveBirchxxx
567ghiabcDaveBirchxxx
123bcdbcdMaryGroveyyy
234abcbcdMaryGroveyyy
456defbcdMaryGroveyyy
567ghibcdMaryGroveyyy
123bcdcdeSriramOakzzz
234abccdeSriramOakzzz
456defcdeSriramOakzzz
567ghicdeSriramOakzzz
123bcddefFangBirchwww
234abcdefFangBirchwww
456defdefFangBirchwww
567ghidefFangBirchwww
123bcdhijGirferdBirchttt
234abchijGirferdBirchttt
456defhijGirferdBirchttt
567ghihijGirferdBirchttt

Write the result of the query: Own Person (Natural Join)

VRNSSNSSNNameAddPhone
234abcabcDaveBirchxxx
123bcdbcdMaryGroveyyy
456defdefFangBirchwww