For this assignment you will work in your assigned small group of 2 or 3 students. You may not work with other groups, and the share of workload must be even between all group members. Failing to do either is a violation of the department Academic Integrity policy. Please read over Expectations for Working with Partners on CS Lab work

Lab 2 Group List for Lab Section A
Lab 2 Group List for Lab Section B

---

Lab 2 Goals

The goals of this lab assignment are to:

1. Design and ER model for a description of data and its use.
2. Translate an ER model to a Relational model
3. Define relations and constrains in SQL
4. Get some practice using sqllite

Both you and your partners should find your Lab2-partner1-partner2-partner3 git repo off the GitHub server for our class: CS44-f16

Next, clone your Lab 2 git repo into your cs44/labs subdirectory, cd into your repo:

cd
cd cs44/labs
git clone [the ssh url to your repo]
cd Lab2-partner1-partner2-partner3

Here are some instructions on Using Git page (follow the instructions for repos on Swarthmore's GitHub Enterprise server).

If all was successful, you will have cloned your Lab2 starting point with a .gitignore file for latex, and a README.md file into which you should enter your names, late days, and answer some questions about this lab after you have completed it.

This lab is different from the previous labs in that it has no programming. Instead, you solve problems by applying ER and Relational data base design, and you will get some practice defining relations in SQL using the sqlite3 system. We will revisit SQLite in more detail later in the semester.

Your group will submit a single solution to these problems. You may use any software you'd like for writing up your solution, but you must submit ER diagrams as figures. Thus, you will need to use some figure drawing software like powerpoint, xfig, gimp, google docs, ...., to create a pdf of the ER diagrams.

I encourage you to try using latex for your lab write-up. Using latex is not required, but if you'd like to try it out, here is an example latex document that you could use as a starting point for your write-up:

cp /home/newhall/public/latex_examples/report/* .

Some information about using latex is available off my help pages: latex, openoffice, xfig, gimp

Working with your group

I suggest two effective ways to work on this lab with your partner(s):

1. Work together from the start, solving each problem on the lab assignement together completely.
2. Try solving a problem or two independently, then meet with your partners and together come up with your joint solution to a problem from your independent attempts at the problem. Then move on to the next problem, solve independently, come together and create a joint solution. and so on.

In both of these methods you should be frequently working together with your partner(s) and every member of your partnership should be involved in solving every problem; it is not in your best interest to only work on a subset of these problems.

You will submit a single ERModel.pdf file with your answers to the following questions:

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. Books have the same attribute above, and for each we keep track of its transactions (i.e., daily sales). Each transaction has a date and quantity, neither of which is unique. But each book only has one daily entry. Draw the corresponding diagram.
3. Lastly, in this modern age, our database should keep track of whether a book is electronic or physical. Draw a diagram for books (which have similar attributes as above), where electronic books have a file size and physical books have a weight. Also, we would like to note relationships between electronic books and the platforms they can be used on. Platforms include a company and webpage (unique). E-books are limited to at most one platform. You do not need to model any other requirements from (a) or (b).

Answer the following questions. You can submit your answers either in a relationalModel.pdf file, or just as an ascii file relationalModel.txt (if you write them up in vim or emacs).

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.
   

You will create a new database named lab2.db for this problem. If you run into errors in sqlite, you can always rm lab2.db and start again. Refer to the Week 4 lab page for sqlite examples and references.

sqlite3 lab2.db

1. Create a new database with following Relations in sqllite (make reasonable guesses as the types of different fields):

   Employees(eid, name, salary)
   Department(did, dname, address)
   WorksAt(empid, deptid, date_hired)

   List the schema in sqlite to verify that you have correctly created these relations.

2. Add relation instances to your database. Add 8 Employee instances, 3 Department instances, and make sure that each Employee you add works at some department. You may choose any values for the relation instance attributes that you'd like, so long as they satisfy the given constraints. Keep in mind that your professor will be viewing your resulting database.

   I recommend that you use bulk loading from files for this part. Create 3 ascii files for each relation, each containing relation instances. Use the $ delimiter. Then try bulk loading them into your lab2.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 relation instances to these three relations.

3. Run the following query on your resulting database:

   SELECT E.name, D.dname
   FROM  Employees E, Department D, WorksAt W
   WHERE E.eid = W.empid AND D.did = W.deptid; 
   

   It should produce a result relation consisting of the name of each employee and the name of the department in which they work.

• This week's lab example: Week 4 in-lab
• sqlzoo.net SQL Tutorial
• sqlite.org
• an SQLite Tutorial

Before the Due Date, push your solution to github from one of your local repos to the GitHub remote repo. Only one of you and your partners needs to do this, but it doesn't hurt if you all do.

From your local repo (in your ~you/cs44/labs/Lab02-partner1-partner2 subdirectory)

make clean  
git add ERModel.pdf 
git add relationalModel.txt
git add lab2.db
git commit -m "solutions for lab 2"
git push

If you do your write-ups in latex, then add to your git repo the latex and image sources and the Makefile (you do not need to add the generated .pdf file containing your answers, as long as you add, commit, and push all of the components that I need to build it):

git add Makefile
git add ERModel.tex   # or whatever you named this file
git add fig1.pdf      #       ""        ""
...

If that doesn't work, take a look at the "Troubleshooting" section of the Using git page. Also, be sure to complete the README.md file, add and push it.