Type your answers below each question. Make sure to provide detailed answers. Your final submission will contain two links - one link to a Google Doc with your responses and the second link to a Draw.io diagram.
Question 1 (20 points): Consider the relational database below. What are the appropriate primary keys for each table/relation? Explain your answer and explain what assumptions you had to make to identify the primary key.
employee (person_name, street_address, city, state, zip, phone1, fax, email)
employment (person_name, company_name, salary)
company (company_name, city, state, zip, phone, fax)
Question 2 (20 points): Identify the relationships between the following entities. Provide detailed explanation for each answer.
1. Prescription/medication
2. Student/course
3. Student/academic advisor
4. Bank account/customer
5. Rental car / renter (the person who rents a car).
6. Animal / food(s) that the animal consumes (assume you are developing a database for a zoo)
Question 3 (60 points): Using draw.io diagram tool, design a database schema for an application that tracks and manages students’ applications for a university. You have complete freedom in how you want to design your database, but you need to justify your decisions. Make sure to create proper primary/foreign key relationships between your tables. All entities and attributes must be named using the underscore convention. Highlight primary keys in red, foreign keys in blue. Document your design decisions in a Google Doc.
Additional Instructions
To setup draw.io:
1. If you don’t have a Gmail account, register for one
2. Go to drive.google.com and login using your Gmail credentials
3. Click on New → More → Connect more apps…
4. In the search field, search for “draw.io”
5. Click “Connect” button next to “draw.io” search result
6. Draw.io will appear in your connected Google Drive apps
7. To launch a new Draw.io diagram, click New → More → draw.io Diagrams
To submit your lab assignment:
1. With your draw.io assignment open, click the blue “Share” button in the top right corner
2. Click “Get shareable link”
3. Change sharing settings to “Anyone with the link can edit”
4. Copy the share link and submit it via CourseWeb
5. Note: follow the same sharing instructions for Google Docs and draw.io diagrams
