Question 1: 10pts Design a database for the St. Joseph County Elections Board, including information about the Precinct, Candidates, Offices, Poll-Workers, Polling-Place, Voters, and Ballots. Whether these are entity sets or relationships is up to you.
Voters cast ballots in an election based upon where they live (their precinct). Different precincts can have different candidates, especially for local offices. Poll-Workers work at a Polling-Place and must to be registered to a party (law in Indiana). A voter’s ballot is determined by the precinct in which they live and the candidates running for offices in their precinct. A Polling-place is always in some precinct, but a voter does not have to vote in the precinct in which they live.
Draw the E/R Diagram for this database. Be sure to include constraints and arity where appropriate.
Question 2: 5pts Change your diagram (from Q1) to include a new Entity Set and Relationship of your choice. Describe this addition in your own words.
Question 3: 5pts One way to represent “Students” and the grades they get in “Courses” is to use entity sets corresponding to “Students”, “Courses”, and “Enrollments”. Enrollment entities form a connecting entity set between Students and Courses. Enrollment entities can be used to represent not only the fact that a student is taking a certain course, but the grade of the student in the course. Draw an E/R Diagram for this situation, indicating weak entity sets and the keys for the entity sets.
Question 4: 5pts Consider again the example from Q1/Q2 of the Election Schema. Offices are typically broken down into their jurisdiction: City, County, State, National. Reconsider your answer to Q2 and this time use sub-entity sets for CityOffice, CountyOffice, StateOffice, NationalOffice. Redraw relationships in the diagram to make it more faithful to how you see elections operating.
Hint: Remember that a sub-entity must always have a reason to exist, i.e., at least one attribute or relationship in it that is not in its parent.
Question 5: 5pts We only had time to talk about the standard (Chen) notation for ER Diagrams, but there exist many other ways to create ER Diagrams. Some are more expressive than others, but they basically do the same thing. Use your Google skills and read up on Crows-foot notation. Convert your answer to Q4 into Crows Foot notation.
Question 6: 10pts Now let’s consider a banking domain. For the relations Accounts and Customers here:
| acctNo | type | balance |
|---|---|---|
| 12345 | savings | 12000 |
| 23456 | checking | 1000 |
| 34567 | savings | 25 |
| firstName | lastName | ID | account |
|---|---|---|---|
| Robbie | Banks | 901222 | 12345 |
| Linda | Hand | 805333 | 12345 |
| Linda | Hand | 805333 | 23456 |
6a: Suppose string attributes (type, firstName, lastName) are all defined as CHAR(25). How much disk space will the tuples in each relation occupy? Show your work.
6b: Suppose string attributes are all defined as VARCHAR(40). How much disk space will the tuples in each relation occupy? Show your work.
6c: When might we choose CHAR instead of VARCHAR? Why?
Question 7: 5pts In this exercise we introduce one of our running examples of a relational database schema. The database schema consists of four relations, whose schemas are:
Product (maker, model, type)
PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
The Product relation gives the manufacturer, model number and type (PC, laptop or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types (that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number). The PC relation gives for each model number that is a PC the speed (of the processor, in GHz), the amount of RAM (in MB), the size of the hard disk (in GB), and the price. The Laptop relation is similar, except that the screen size (in inches) is also included. The Printer relation records for each printer model whether the printer products color output (true, if so), the process type (laser or ink-jet, etc), and the price.
Write SQL declarations for each relation.
Note: remember to determine primary keys for each relation
Question 8: 5pts (from Garcia-Molina, p165) Convert this E/R Diagram below to a relational database schema.
Question 9: 5pts (from Garcia-Molina, p165) This E/R Diagram below represents ships. Ships are said to be sisters if they were designed from the same plans. Convert this diagram to a relational database schema.
Question 10: 5pts Convert your answer to Q4 to a relational database schema.
Some exercises are from Garcia-Molina, Ullman, Widom. Database Systems. 2nd Edition.
This is an individual assignment. Discussion is allowed, but students may not write submitted solutions in the presence of a group.
Cite your sources.
Submissions must be uploaded to GradeScope and marked by the due date.