Chris Pollett> Old Classses >
CS157a

( Print View )

Student Corner:
[Submit Sec4]
[Grades Sec4]

[Lecture Notes]
[Discussion Board]

Course Info:
[Texts & Links]
[Description]
[Course Outcomes]
[Outcomes Matrix]
[Course Schedule]
[Grading]
[Requirements/HW/Quizzes]
[Class Protocols]
[Exam Info]
[Regrades]
[University Policies]
[Announcements]

HW Assignments:
[Hw1] [Hw2] [Hw3]
[Hw4] [Hw5] [Quizzes]

Practice Exams:
[Midterm] [Final]

HW#4 --- last modified October 30 2023 08:53:07.

Solution set.

Due date: Nov 13

Files to be submitted:
  Hw4.zip

Purpose: To gain experience with the E/R to relational mapping and with writing relational algebra and SQL Queries.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO1 -- Write relational algebra queries and predict given a database instance what such a query will return.

CLO2 -- Write SQL commands to create databases, create tables, insert/update/delete/retrieve rows in a common database management system.

Specification:

For this homework write up solutions to the following problems and put them in a file Hw4.pdf which you include in the Hw4.zip file that you submit. Make sure you also include in the Hw4.zip file a readme.txt file listing all your team mates and their IDs.

  1. Imagine you were tasked with designing a database system for an online library catalog for a city. It should be capable of storing information about the books and media the library has as well as users of the library. Create an E/R diagram modeling the data that would need to be stored. Redo this problem, but where instead you create a UML diagram for the same thing. Your diagrams should make use of weak entity sets, isa relationships, keys, a referential integrity constraint, and display degree constraints.
  2. For each of Figures 4.2, 4.5, 4.8, and 4.11 in the book apply the algorithm from class to map the E/R diagram to a relational schema.
  3. Draw each of Figures 4.2, 4.5, and 4.8 as a UML diagram, show the result of mapping your UML diagram for 4.17 to relations according to the algorithm from class for UML mapping.
  4. Do exercise 5.2.1 a,d,h,m from the book, but where we add the tuple (2,7) to S(B,C).
  5. Do exercise 5.3.1 a,b from the book.
  6. If you don't have a copy of Mysql on your machine, please install it. For this problem I want you to make a text transcript of doing the following actions from the Mysql shell on a Mysql database as well as their outputs. Create the tables of Exercise 6.1.3 in a database called ComputerStore. Insert at least three rows into each table. Do Exercise 6.1.3 and run each of your SQL queries against your DB and show the results.
Point Breakdown
Problem 1 - each part worth 0.5 1pt
Problem 2 - each mapping worth 0.5 2pts
Problem 3 - each mapping worth 0.5 2pts
Problem 4 - each part worth 0.5 2pts
Problem 5 - each part worth 0.5 1pts
Problem 6 - transcripts of: create db (0.5pts), inserts (0.5pts), queries and results (1pt) 2pts
Total10pts