Database Theory and Practice Homework -

Homework 1: ER Modeling

For each of the following, develop an ER or EER diagram for the situation described. Represent the situation as accurately as possible, including participation, cardinality, and key constraints as appropriate. Also identify any attributes which are required (i.e. should be NOT NULL). Note any constraints present that you did not capture in the diagram along with a reason why they were omitted. If necessary specifics are lacking in the description of the situation, make reasonable assumptions and state those assumptions.

Draw your diagrams by hand (neatly) or use a program such as dia (choose the "ER" symbol set; for EER elements use the attribute symbol for the small circle).

1. Design a database for recording information about the activities (specifically votes) taken in the US House of Representatives, including:

• Each Congress has a unique number (such as 115) and range of dates when it meets (such as January 3, 2017 to January 3, 2019).
• Each US state has a name and region (Northeast, Midwest, Southeast, Southwest, West).
• Each Representative is described by his or her name, represents a particular district in their state, and belongs to a political party (Republican, Democrat, Independent, Other). Note that a Representative's district and/or party affiliation may be different for different Congresses, but assume that it won't change during a single Congress.
• A bill has a unique bill number, a title, at least one sponsor, the Congress it was introduced in, and the date on which it was introduced.
• For each bill that has been voted on, the date of the vote, the number of "ayes" and "nos", and whether the bill passed or failed is recorded. Assume there can only be one vote on a given bill. In addition, how each Representative voted on the bill (yes, no, abstain, absent) is also recorded.

2. Design a database to manage conference papers. Researchers interested in presenting at a conference submit their research papers for consideration. Reviews by reviewers are recorded for use in the paper selection process. The database system caters primarily to reviewers who record answers to evaluation questions for each paper they review and make recommendations regarding whether to accept or reject the paper. The data requirements are summarized as follows:

• Authors of papers are uniquely identified by email id. First and last names are also recorded.
• Each paper is assigned a unique identifier by the system and is described by a title, abstract, and the name of the electronic file containing the paper.
• A paper may have multiple authors, but one of the authors is designated as the contact author.
• Reviewers of papers are uniquely identified by email address. Each reviewer's first name, last name, phone number, affiliation, and topics of interest are also recorded.
• Each paper is assigned between two and four reviewers. A reviewer rates each paper assigned to him or her on a scale of 1 to 10 in four categories: technical merit, readability, originality, and relevance to the conference. Finally, each reviewer provides an overall recommendation regarding each paper.
• Each review contains two types of written comments: one to be seen by the review committee only and the other as feedback to the author(s).

3. Design a database to keep track of information for an art museum:

The museum has a collection of art objects, each of which has a unique ID number, an artist (if known), the year it was created (if known), a title, and a description. In addition, there is a country or culture of origin (Italian, Egyptian, American, Indian, etc) and time period (Renaissance, Modern, Ancient, etc).

For artists, there is a name, date of birth (if known), date of death (if not living), country, period (Renaissance, Modern, Ancient, etc), main style, and description. The name is assumed to be unique.

An art object is either a painting, a sculpture, a statue, or "other" (for objects that don't fall into one of the other three categories).

• A painting has a type (oil, watercolor, etc), material (paper, canvas, wood, etc), and style (modern, abstract, etc).
• A sculpture or statue has a material (wood, stone, etc), height, weight, and style.
• All other art objects have a type (print, photo, etc) and style.

Art objects either belong to the permanent collection or are borrowed.

• For objects in the permanent collection, the date acquired, status (on display, on loan, or stored), and cost are recorded.
• For objects on loan, the museum that the object is loaned to and the loan period (start and end dates) are recorded. The museum is identified by a name; contact information (a name, an address, and a phone number) and a description are also recorded.
• For borrowed objects, the owner, date borrowed, and date returned (if applicable) are recorded. The owner of a borrowed object can be an individual or another museum. In both cases, there is a name (unique) and contact information (a name, an address, and a phone number). For a museum, there is also a description.

Different exhibitions occur, each having a name, start date, and end date. Any object can be part of an exhibition.

4. An automobile repair shop needs to keep information about its operations. Customers initially bring their cars to the shop for an estimate of repairs. A mechanic looks at the car and estimates the cost and time required for the entire job. A job might include several repairs (e.g. replacing the left fender, painting the passenger door) and may include both body work and mechanical repairs. If the customer accepts the estimate, a job number is assigned and the customer's name and contact information; the car's license plate number, make, model, and year; and a list of the repairs needed are recorded. The customer then makes an appointment to bring in the car on a specified date. When the car is brought in for repairs, a technician and/or a mechanic is assigned to work on the car. (Body work can be done by a technician or a mechanic, but mechanical repairs require a licensed mechanic.) The shop keeps track of the charges for parts and labor as they accumulate. The time actually spent for each repair is recorded and used to calculate the cost of labor, using a fixed hourly rate. When the work is complete, the shop produces an itemized bill for the customer showing the part(s) required, the cost of each part, the total labor hours and cost, and the name of the technician/mechanic who completed that repair for each of the repairs in the job.

Homework 2: ER → Relational Mapping and Normalization

1. Map the ER schema below into a relational schema. (Click for a larger version.) For any case where you have a choice of ways to map a particular construct, choose the mapping that you think is best for this situation and explain your decision. Also be sure to specify all primary key and referential integrity (foreign key) constraints.

2. Map the ER schema below into a relational schema. (Click for a larger version.) For any case where you have a choice of ways to map a particular construct, choose the mapping that you think is best for this situation and explain your decision. Also be sure to specify all primary key and referential integrity (foreign key) constraints.

3. Consider the following relation about students and courses:

COURSE(dept,number,studentID,firstname,lastname,instructor,time,room,grade)

The functional dependencies:

• dept,number → instructor [a course has one instructor]
• dept,number → time,room [a course is taught at a particular time and in a particular place]
• studentID → firstname,lastname [studentID identifies a student]
• dept,number,studentID → grade [a student has a single grade in a course]

Answer the following questions:

a. What normal form is this relation in? Explain your answer.

b. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

4. Consider the following relation about students:

STUDENT(studentID,firstname,lastname,major,credits,status)

The functional dependencies:

• studentID → firstname,lastname,major,credits [studentID identifies a student]
• credits → status [a student's status is based on how many credits they have]

Answer the following questions:

a. What normal form is this relation in? Explain your answer.

b. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

5. Consider the following relation containing information about student room and board arrangements:

ROOM_BOARD(firstname,lastname,studentID,homeAddr,homePhone,dorm,room,                 dormAddr,status,mealPlan,roomCharge,mealPlanCharge)

• Each student is assigned to one dorm room. Several students may be assigned to the same room.
• Names of students are not unique.
• The college has several dorms. dorm contains the name of the dorm and room contains the number of the particular room assigned to the student. Dorm names are unique.
• dormAddr is the address of the dorm. Each building has its own unique address.
• status tells the student's status: first year, sophomore, junior, senior.
• mealPlan tells how many meals per week the student has chosen as part of his or her meal plan. Each meal plan has a single mealPlanCharge associated with it.
• The roomCharge is different for different dorms, but all students in the same dorm pay the same amount.

Answer the following questions:

a. Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation.

b. What are the candidate keys for this relation? Identify the primary key. (If there is more than one candidate key, explain why you picked the primary key you did.)

c. What normal form is this relation in? Explain your answer.

d. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

Homework 3: SQL

Express each of the following queries using SQL. Make sure that your queries work no matter what rows are in the tables. Avoid duplicates in the results (unless otherwise specified), but you should only explicitly eliminate duplicates if duplicates are possible. Any correct query is acceptable, but you should try to write as simple a solution as you can. (Simpler solutions are easier to reason about in order to check their correctness.)

Use the following database schema:

• BOOK(Book_id,Title,Publisher_name)
• BOOK_AUTHORS(Book_id,Author_name)
• PUBLISHER(Name,Address,Phone)
• BOOK_COPIES(Book_id,Branch_id,No_of_copies)
• BOOK_LOANS(Book_id,Branch_id,Card_no,Date_out,Due_date)
• LIBRARY_BRANCH(Branch_id,Branch_name,Address)
• BORROWER(Card_no,Name,Address,Phone)

Note that BOOK_COPIES only contains entries where No_of_copies is at least 1 - if a branch doesn't contain any copies of some book, there is no entry in BOOK_COPIES (not an entry with 0 copies).

Key attributes are underlined. The foreign key constraints are the following:

• The Publisher_name in BOOK refers to the Name in PUBLISHER.
• The Book_id attributes in BOOK_AUTHORS, BOOK_COPIES, and BOOK_LOANS all refer to the Book_id in BOOK.
• The Branch_id attributes in BOOK_COPIES and BOOK_LOANS both refer to the Branch_id in LIBRARY_BRANCH.
• The Card_no in BOOK_LOANS refers to the Card_no in BORROWER.

You can test your queries for 1-17 (not the data modification statements) using the ex_library database, but remember that a correct answer must work regardless of the specific data in the database.

1. Display all of the information in the BORROWER table.

2. Find all of the book titles, in alphabetical order.

3. Display the title, due date, and borrower name for all books that have been checked out from Wood Library, with the latest due date first. List books with the same due date in order by borrower name, and those with the same due date and borrower name in order by title.

4. Display all of the author names.

5. Find the name, address, and phone number for all borrowers with a 789 area code.

6. Find the names and card numbers of borrowers who have checked out books from more than one branch, in order by card number.

7. Find the books (book titles and authors) not written by C.J. Cherryh, in order by author and title.

8. Find the names of library branches which do not have at least one copy of the book Mission Child, in order by branch name.

9. Find the names of library branches which have copies of all of the books, in order by branch name.

10. Find the title of the book which is alphabetically first of all of the books.

11. Find the book, its author(s), and library branch name for the book with the highest number of copies at a branch. If there's a tie, include all of the books/branches with the highest number of copies.

12. For each branch (branch ID and name), find the number of books checked out on 2006-10-14.

13. Find the branches (branch ID and name) with copies of at least 10 different books (and how many different books those branches have copies of).

14. For each branch (branch ID and name), find the total number of books (counting all of the copies of each book) that branch has.

15. Find the publisher who has published the largest number of books.

16. Find the titles of all of the books written by the author(s) who have written the largest number of books.

17. For each branch and book, find the number of copies of the book still on the shelf at the branch on 2006-10-14. (The number still on the shelf is the number of copies the branch has that haven't been checked out.)

18. Delete any entries in BOOK_COPIES where the number of copies is 0.

19. Add 2 copies to each book owned by Wood Library.

20. Change the due date of any book checked out from Wood Library that is due before 2006-11-05 to 2006-12-31.

Solutions should be in the same format as the reference solutions. Solutions cannot be exactly the same as reference solutions.

Attachment:- Assignment File.rar