Creating A SQL Db From Entity Relationship Diagram, Instructions

User Manual:

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

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.&
!
!
!
!
!
!
!
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:
Student Course
Dept
StudentID
first
name
last
name
gender
phone
number
takes
CourseID
DeptID
Name
Offers
N
Credit
Hours
phone
type
1
has
N
N
Phone
N
grade
Is a Prereq
of?
1
N N
!
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.

Navigation menu