CS 4347 — Database Systems, Homework 3
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
| VRN | Ma | Mo | Color |
|---|---|---|---|
| 123 | Honda | Hawk | Red |
| 234 | Mazda | RX7 | Blue |
| 345 | Ford | Taurus | Blue |
| 456 | Ford | Ranger | Green |
| 890 | Suzuki | Intruder | Black |
Own
| VRN | SSN |
|---|---|
| 123 | bcd |
| 234 | abc |
| 456 | def |
| 567 | ghi |
Person
| SSN | Name | Addr | Phone |
|---|---|---|---|
| abc | Dave | Birch | xxx |
| bcd | Mary | Grove | yyy |
| cde | Sriram | Oak | zzz |
| def | Fang | Birch | www |
| hij | Gilfrd | Birch | ttt |
Write a relational algebra query to return all tuples of Vehicle relation with Ma = ‘Honda’
(Vehicle)
- (123, ‘Honda’, ‘Hawk’, ‘Red’)
Write a relational algebra query to return the VRN and Color of Vehicles with Ma = ‘Honda’
(Vehicle))
- (123, ‘Red’)
Write the result of the query: Vehicle x Own (Cartesian Product)
| VRN | Ma | Mo | Color | VRN | SSN |
|---|---|---|---|---|---|
| 123 | Honda | Hawk | Red | 123 | bcd |
| 123 | Honda | Hawk | Red | 234 | abc |
| 123 | Honda | Hawk | Red | 456 | def |
| 123 | Honda | Hawk | Red | 567 | ghi |
| 234 | Mazda | RX7 | Blue | 123 | bcd |
| 234 | Mazda | RX7 | Blue | 234 | abc |
| 234 | Mazda | RX7 | Blue | 456 | def |
| 234 | Mazda | RX7 | Blue | 567 | ghi |
| 345 | Ford | Taurus | Blue | 123 | bcd |
| 345 | Ford | Taurus | Blue | 234 | abc |
| 345 | Ford | Taurus | Blue | 456 | def |
| 345 | Ford | Taurus | Blue | 567 | ghi |
| 456 | Ford | Ranger | Green | 123 | bcd |
| 456 | Ford | Ranger | Green | 234 | abc |
| 456 | Ford | Ranger | Green | 456 | def |
| 456 | Ford | Ranger | Green | 567 | ghi |
| 890 | Suzuki | Intruder | Black | 123 | bcd |
| 890 | Suzuki | Intruder | Black | 234 | abc |
| 890 | Suzuki | Intruder | Black | 456 | def |
| 890 | Suzuki | Intruder | Black | 567 | ghi |
Write the result of the query: Vehicle Own (Natural Join)
| VRN | Ma | Mo | Color | VRN | SSN |
|---|---|---|---|---|---|
| 123 | Honda | Hawk | Red | 123 | bcd |
| 234 | Mazda | RX7 | Blue | 234 | abc |
| 456 | Ford | Ranger | Green | 456 | def |
Write the result of the query: Own x Person (Cartesian Product)
| VRN | SSN | SSN | Name | Add | Phone |
|---|---|---|---|---|---|
| 123 | bcd | abc | Dave | Birch | xxx |
| 234 | abc | abc | Dave | Birch | xxx |
| 456 | def | abc | Dave | Birch | xxx |
| 567 | ghi | abc | Dave | Birch | xxx |
| 123 | bcd | bcd | Mary | Grove | yyy |
| 234 | abc | bcd | Mary | Grove | yyy |
| 456 | def | bcd | Mary | Grove | yyy |
| 567 | ghi | bcd | Mary | Grove | yyy |
| 123 | bcd | cde | Sriram | Oak | zzz |
| 234 | abc | cde | Sriram | Oak | zzz |
| 456 | def | cde | Sriram | Oak | zzz |
| 567 | ghi | cde | Sriram | Oak | zzz |
| 123 | bcd | def | Fang | Birch | www |
| 234 | abc | def | Fang | Birch | www |
| 456 | def | def | Fang | Birch | www |
| 567 | ghi | def | Fang | Birch | www |
| 123 | bcd | hij | Girferd | Birch | ttt |
| 234 | abc | hij | Girferd | Birch | ttt |
| 456 | def | hij | Girferd | Birch | ttt |
| 567 | ghi | hij | Girferd | Birch | ttt |
Write the result of the query: Own Person (Natural Join)
| VRN | SSN | SSN | Name | Add | Phone |
|---|---|---|---|---|---|
| 234 | abc | abc | Dave | Birch | xxx |
| 123 | bcd | bcd | Mary | Grove | yyy |
| 456 | def | def | Fang | Birch | www |