1 a) We wish to design a database representing cities, counties, and states in the US. For states, we wish to record the name, population, and state capital (which is city). For counties, we wish to record the name, the population, and the state in which it is located. For cities, we wish to record the name, the population, the state in which it is located and the county or counties in which it is located. Names of states are unique. Name of counties are unique whithin a state (e.g. 26 counties have Washington Counties)m and cities are likewise unique only within a state (e.g. there ae 24 Springfields among 50 stattes). Some counties ans cities have the same name, even withing a state (example: San Francisco). Almost all cities are located withing a single county, but some (e.g. New York City) extend over several counties.
Draw an entity/relationship diagram representing the above information. Be sure to indicate keys, multiplicity of relationships (e.g. many-to-one), and weak entity sets.
b) Convert your design from (a) into relations,
c) Represent the same information as in (a) by an ODL design.
d) Convert your design from (c) into relations.
2. Consider the relation schema R (A, B, C, D, E) with fuctinal dependecies AB --> C, C --> D, C --> E, D --> A and A --> B.
a) Find all the (minimal) keys for R.
b) Which of the given dependencies violate BCNF?
c) Using one of your violating dependencies from (b), decompose R into two relations.
3. Suppose we have a database schema
Babies (bc, fn, ln, dob)
Rooms (bc, date, roomNo)
The four attributes of Babies represent "bith certificate number" (assumed unique)m "first name", "last name", and "date of birth", respectively. A tuple (c, f, l, d) of this relation means that the baby with birth certificate cm first name f, last name l, was born on day d. A tuple (c, d, r) of relation Rooms represents th fact that the baby with birth certificate c occupied hospital room r (assumed unique) on date d (not necessarily date of birth),
Write the followinf SQL queries and commands,
a) List, in decreasing orderm the birth certificate numbers of the babies born 5/8/97.
b) Find the room numbers occupied by babies with last name "aiken".
c) Find the "twins", pairs consisting of first names of babies born with the same last name on the same say. You should list each pair in only one order.
d) Find all the babies without room assignments. That is, produce the birth certificates of the babies mentioned in Babies but not in Rooms.
e) Find the day on which the largest number of babies were born and the number of babies born on that day.
f) Delete from rooms any tuples that say a baby occupied a room before it was born. (you may assume comparison of dates by < is legal).
4. This question is based on the following ODL schema:
interface Auto (extent, Autos key serial#) {
attribute integer serial#;
attribute string brand;
relationship Owned ownedBy
inverse Owner::owns;
}
interface Owner (extent Owners) {
attribute string name;
attribute string addr;
relationships Set<Auto> owns
inverse Auto::ownedBy;
}
Write the following queries in OQL:
a) Find the names and addresses of the owners of Chevrolets ("Chevrolet" is a brand of auto).
b) Find the brands of automobiles owned by all owners whose name is "Saly". Do not produce the same brand more than once.
Maximum credits: task 1: 20 points, task 2: 12 points, task 3: 18 points, task 4: 10 points