Due Date

Due by 11:59 p.m., Tues, Feb. 18, 2025.

Your lab partner for Lab 3 is listed here: Lab 3 lab partners

Our guidelines for working with partners: working with partners, etiquette and expectations

Overview

This lab involves database design using the ER and Relational Models. You develop an ER design for a database given a list of requirements, you will translate ER to relational, and you will get some some practice using SQLite to define a relational database and to run a few simple queries on it.

This lab does not involve C++ programming, but you will have to use some document producing software, as well as use SQLite. Be sure to complete the Week4 in-lab sqlite tutorial before starting on this assignment.

You will use both git and Gradescope to submit your solution to this lab, so note carefully how to submit your answers to each part.

Goals

  • Gain some experience using the ER model to design a DB given a description of data and its use.

  • Gain some practice with translating an ER model to a Relational model.

  • Practice using sqlite.

  • Practice defining relations and constrainsts in SQL.

Getting started

For this assignment, you will submit some of your answers in gradescope and some in git.

  1. You should begin by bringing up the assignment in gradescope, and one of you should add you and lab partner as group members to this assignment. Lab3 gradescope answers part. Only one of you or your partner will submit your joint solution in gradescope.

  2. Next, get your Lab3 repo:

Find your git repo for this lab assignment off the GitHub server for our class: CS44-s25

Clone your git repo (Lab3-userID1-userID2) containing starting point files into your labs directory:

cd ~/cs44/labs
git clone [the ssh url to your your repo]
cd Lab3-userID1-userID2

If all was successful, you should see the following files (highlighted files require modification):

  • README.adoc - some directions about how to answer and submit labs. Read the README.adoc file.

  • PARTB_answers - the starting point file into which you will add your answers to part B. Do not wrap lines and use formatting that makes your answers easy to read.

  • PARTC_answers - the starting point file into which you will add your answers to part C. Do not wrap lines and use formatting that makes your answers easy to read.

Use any Unix editor program to open the PartC_answers and PartB_answers files to add your answers to these parts of the assignment.

Note that these files already have some content indicating where to enter your answers to specific questions. Do not remove this content, and be sure to insert your answers in the appropriate place in the file.

Part A: ER Model

Your answers to this part should be submitted in the Lab3 gradescope assignment.

Your answers to this part should be submitted in Gradescope. You should upload a separate .pdf file with an image of your ER diagram for each of the three questions below.

You can use software tools like google docs or PowerPoint to draw ER diagrams and create pdfs to submit. A pdf from a photo/scan of a hand-drawn ER diagram is also acceptable to submit, but it must be legible.

Check your submitted answers in Gradescope to ensure that they are readable.

Submit a separate ER diagram for each of the following:

  1. You have been tasked with modeling the book industry. Draw an ER diagram to model the following: books have a title, ISBN number (which is unique for each book), and subject. Books usually cite other books (i.e., references). In addition, each book must have at least one author, who has a Social Security number (unique for each individual), name, and a phone number. We would like to keep track of bookstores, who sell books, and have a store id, address, and name.

  2. As a separate problem, we would like to keep track of all book sales. An Invoice records a unique instance of selling a set of books. Each Invoice has a unique invoice number, a total sales amount, the date of purchase, and the customer’s name (does not need to be unique). Books have the same attribute as above. Each invoice is associated with one or more different books, where for each unique book sold as part of a specific invoice the number of copies of that book is recorded. (Note: you do not need to model the customer beyond their name being part of an invoice for this problem).

Part B: ER to Relational

Your answers to this part should be submitted in the PartB_answers file in your git repo.

  1. Briefly, explain how views provide logical data independence.

  2. Show the SQL statements for converting the following ER diagram to relations. Indicate any constraints in the ER diagram that you cannot capture in the SQL statements.

ER diagram for part B

Part C: Sqlite

Your answers to this part should be submitted in the PartC_answers file in your git repo. You should also add your sqlite database files to your repo for the University and Restaurant DBs.

University DB

First complete the University DB part of the sqlite tutorial: (universityDB from sqlite tutorial).

  1. You should copy your universityDB file into your Lab repo and add and commit it to your repo. For example, if my universityDB file is in my cs44/weeklylabs/week04 subdirectory and I’m currently in my Lab3 repo directory, I would do the following:

    cp ~/cs44/weeklylabs/week04/universityDB ./
git add universityDB
    .Then complete any outstanding parts that you did not finish, and if you
modify this file, be sure to add changes to git again:
    sqlite universityDB
git add universityDB

Restaurant DB

Next, solve this problem from within your your Lab3 repo.

Building off the SQLite tutorial, you will construct and submit restaurant.db, a database modeling the restaurant industry.

First, create your database (run this from within your Lab repo):

sqlite3 restaurant.db

Note: if you need to start over, you can always quit sqlite and delete your previous database:

sqlite> .exit
$ rm restaurant.db

  1. Create a new database with following relations, keys in bold (make reasonable guesses as the types of different fields):

    • Customer(id, name, budget)

    • Restaurant(id, name, address)

    • DinesAt(custid, restid, date)

      Note that there are two references in these relations to connect customer ids and restaurant ids to the DinesAt relation.

      Copy and Paste the SQL statements to create the relations into the into the appropriate part of PartC_answers file.

      List the schema in sqlite to verify that you have correctly created these relations. And copy and past the sqlite command and output

  2. Add instances to your database. Add 8 Customer instances, 3 Restaurant instances, and make sure that each customer you add dines at some restaurant. You may choose any values for the relation instance attributes that you’d like, so long as they satisfy the given constraints (and keep in mind that your instructors are going to view the values you choose to enter).

    I recommend that you use bulk loading from files for this part. Create an ASCII file for each relation, each containing relation instances. Then try bulk loading them into your restaurant.db database. You could also run INSERT INTO SQL statements to add values in one at a time.

    Run SELECT queries to list the relation instances from each relation to verify that you have correctly added your data.

    Copy and Paste the SQL command you entered to list your relations into the appropriate part of PartC_answers file.

  3. Run the following query on your resulting database:

    SELECT C.name, R.name
FROM  Customer C, Restaurant R, DinesAt D
WHERE C.id = D.custid AND R.id = D.restid;

    It should produce a result relation consisting of the name of each customer and the name of the restaurant in which they dine.

    Copy and Paste the output of this query to the appropriate part of PartC_answers file

  4. Are the schema as given to you in this problem a good design for this database? If yes, explain why. If no, explain why not and suggest one change to the schema that would fix one problem you identify. Note: this question is not asking you to think of additional attributes/fields (like "party size") to add, but is asking you to assess the relational design of the set of data that is being modeled by this schema. You do not need to write a lot for your answer. A couple concise sentences is sufficient.

    Enter your answer in the appropriate part of PartC_answers file.

Submit

  • Submit Part A in gradescope before the due date.

  • Submit Part’s B and C in your git repo before the due date.

    From your local repo (in your ~/cs44/labs/Lab3-userID1-userID2 subdirectory)

    git add  PARTB_answers  PARTC_answers  UniversityDB  ResturantDB
git commit -m "my correct and well commented solution for grading"
git push

    Verify that the results appear (e.g., by viewing the the repository on CS44-s25). You will receive deductions for submitting code that does not run or repos with merge conflicts. Also note that the time stamp of your final submission is used to verify late days, so please do not update your repo until after the late period has ended.

    If that doesn’t work, take a look at the "Troubleshooting" section of the Using git for CS44 labs and the Using git pages.

  • After submiting your solution, submit the required Lab 3 Questionnaire (each lab partner must do this).

Resources

SQL