Creating A SQL Db From Entity Relationship Diagram, Instructions

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 2

DownloadCreating A SQL Db From Entity Relationship Diagram, Instructions
Open PDF In BrowserView PDF
CS504
Spring 2017
Homework 2 (v2) DUE Feb 19th, 2017 by 11:59 PM
Please submit a single PDF for the text of your answers (1a) and include a statement
whether you are using Oracle or MySQL. Please create separate script files for the
SQL in 1b and problem 2, and include the log (spool/tee) files for each of these
steps. In total, you should submit 5 files (1 PDF, 2 SQL, and 2 SQL logs). The grader
will run your SQL, so, make sure it works.

first
name

StudentID

Credit
Hours

last
name

N
gender

Student

N
takes

1

CourseID

N
Is a Prereq
of?

Course

N

N

N
Phone

has

grade

Offers

1
Dept
phone
number

phone
type

DeptID

Name

Problem 1 (24 points, 3 points for each entity and 3 points for each relationship. To be
correct, be sure to include all text (part a) and SQL(part b) for all items listed below).
For the ER diagram above:
a) Convert the entity-relationship design to a schema for a relational database. List all
relational schema. For each relational schema, state
(i) the name of the relation,
(ii) the names of its attributes,
(iii) the domain (or data type) of each attribute,
(iv) the primary key, and
(v) the foreign key(s).
b) Using Oracle/MySQL on VSE LAB machine or the VM provided, create the SQL to
create each table from the relational schema defined above. Implement all these in one
script (text) file that can be submitted. In the beginning of your script, drop all tables that
you are about to create:

Oracle example: drop table student cascade constraints;
MySQL (child tables must be dropped before parents): drop table student;
Make sure that the script runs on sqlplus or mysql (i.e. check that the tables and keys are
created properly).
Some Tips:
1. If you have trouble creating a particular table, try a different name. A name (like
order, group, user, etc) may be a reserved word.
2. When creating tables that contain foreign keys, make sure the tables that are
referenced have already been created.
3. If you edit your SQL in MS Word, be aware that it may try to "pretty" up your
single quotes, which will cause errors (since a backwards quote is not accepted).
Please use the spool and set echo on commands in Oracle (tee in mysql) to capture the
output of your scripts. Turn in both the sql text file (file with the SQL commands) and
the script file showing the output of running the script. For an example of how to do
this, see the class website.
Problem 2 (12 points, 2 points each).
Load the example system which has been provided for both Oracle
(http://kevinmolloy.info/teaching/cs504/hw/hw2_testbed_oracle.sql) and MySQL
(http://kevinmolloy.info/teaching/cs504/hw/hw2_testbed_mysql.sql) databases. To load
the data into the system, you will need to use sqlplus (oracle) with the (@ command) or
mysql (utilizing the source command).
Write the following queries in SQL.
1. Find the UPCs of all alcoholic beverages (category = ‘Alcohol’).
2. Find the names (first and last names) of all customers who are 21 years or older and
live in Fairfax.
3. Find all customers (customer_ID) who purchased something and live in Fairfax
(zip_code ‘22030’)
4. Find transactions that contain alcoholic beverage(s).
5. Find all customers (customer_ID) who bought alcoholic beverages.
6. Find the customer_IDs of customers who purchased Pepsi or Coke products
(brand = ‘Pepsi’ OR brand = ‘Coca Cola’). Try this with and without UNION.
Source Exif Data: 
File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.3
Linearized                      : Yes
XMP Toolkit                     : Adobe XMP Core 5.6-c016 91.163616, 2018/10/29-16:58:49
Create Date                     : 2017:02:16 18:49:40Z
Creator Tool                    : Word
Modify Date                     : 2019:02:04 21:05:41-05:00
Metadata Date                   : 2019:02:04 21:05:41-05:00
Keywords                        : 
Producer                        : Mac OS X 10.12.3 Quartz PDFContext
Format                          : application/pdf
Title                           : Microsoft Word - CS504_HW2.docx
Document ID                     : uuid:05208eab-63e2-4923-998d-1c608fc7bf1c
Instance ID                     : uuid:41a230f0-bb57-451f-b8ac-5edef6384414
Page Count                      : 2
Creator                         : Word
EXIF Metadata provided by EXIF.tools

Navigation menu