Relational databases

Anyone great with relational databases?  Need some help.

CMIS320 Project 2

This assignment allows students to demonstrate their skills in the area of designing relational databases
to satisfy specific business rules and requirements. The deliverables for this assignment include an Entity
Relationship Diagram and detailed documentation describing the database design and structure.

In this assignment you will be provided with a description of an application (below) to create an entity-
relationship diagram (ERD) and design accompanying table layout using sound relational modeling
concepts and practices. The relationships between the entities and the attributes for the entities will be
identified and described. This database will provide the foundation for the follow-on assignment. The
following paragraphs provide the background and summary of the business requirements.

You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for
the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos
and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental
forms for years. They have finally decided to automate their record keeping with a relational database.

You sit down with Mom and Pop to discuss their business, and watch their operation for about a week.
You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case
that is rented out. They have several copies of each movie they rent; therefore there are several videos
and DVDs for each movie title. You learn that in their inventory they have several thousand videos and
DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them
are based on the quantity of their shipment and the past business they have done with each company.

The price of a DVD for a movie might be different than the price of a video for the same movie, even from
the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery,
comedy, etc.). A single distributor may provide several different types of movies in both video and DVD
format. It is possible to obtain the same movie from multiple distributors, and at different wholesale
prices.

Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in
addition to the distributor’s serial number for the item. Each movie also has a unique identification number
Mom and Pop assign in addition to the title, and any movie IDs the distributors use in their electronic
catalogs. Distributors provide electronic catalogs to Mom and Pop and the information from these
catalogs must be included in the database.

Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all
customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report
of which videos are returned late because there are standard and late charges. On occasion there are
discount prices for certain movies or types of movies. Customers want to rent movies based on actors or
actresses, running length, type of movie, rating, year released, the director, and the academy awards won
(by the movie, the actors, the actresses and/or the directors). Customers also want to know how many
videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic
information on customers in their database, such as name, address, telephone numbers, etc.

There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have
in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos,
DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors,
actresses, directors, and academy awards (i.e., Oscars). Other types of awards (e.g., Golden Globe,
People’s Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos,
DVDs, popcorn, etc., is not to be kept in the database.

Using this information, you should:

1) Determine and list your entities. Then create relationship sentence pairs between those
entities that are related. You should not have any many-to-many relationships.

2) Create an entity/relationship diagram (ERD) showing all your entities, attributes, and
relationships. Sketch your ERD by hand or use a drawing program. Your diagram must be on a
single page. All entities should be related to at least one other entity. Your ERD should have all
one-to-many relationships and not have any many-to-many relationships.

3) Create metadata that describes the table created from each entity and the column created from
each attribute in the ERD. Particular attention will be given to the proper specification of
all primary key (via “PK”) and foreign key (via “FK”) columns in the table layouts. These should
match your ERD exactly.

Submit everything for the above steps in a single Word or PDF file to the LEO assignment link. Make
sure your work is neat and legible and your steps are in order.

Grading rubric

Attribute Meets Does Not Meet

Entities and attributes 20 points
Student identifies a large potential
set of entities and their attributes
for the application

0 points
Major problems with identification of
entities and their attributes

Relationship sentence
pairs

20 points
Student properly formats all
required relationship sentence
pairs to describe one-to-many
relationships

0 points
Major problem with development of
relationship sentences

ERD 40 points
ERD properly includes and depicts
all entities, attributes and
relationships identified

0 points
ERD is missing many items and/or has
major problems with relationships

Metadata 20 points
Spreadsheet of table specification
metadata properly includes all
tables, their columns, and all
required details for the columns

0 points
Major omissions for table
specification metadata

CMIS320 Project 3

In this assignment you will perform the physical design and implementation using SQL Data Definition
Language (DDL) and proceed with populating the Mom and Pop Johnson Video Store database via Data
Manipulation Language (DML) SQL commands.

For each of the steps below you may create a separate SQL script file and SPOOL file or you may want
to put the SPOOL output for several steps, from the same SQL script file, in the same file. Be sure your
SPOOL file(s) contains your SQL statements along with the Oracle responses and/or displayed
results. Do NOT submit your SQL script files. Only submit your output SPOOL files.

Assignment Details:

1) Create Oracle database tables using SQL Data Definition Language (DDL) for each table listed
in the metadata of Project 2. You may need to use a combination of DROP TABLE, CREATE
TABLE, and ALTER TABLE SQL statements. Make sure that entity and referential integrity are
enforced by declaring a primary key for each table (these may be composite keys) and
declaring all appropriate foreign keys. Your CREATE TABLE and ALTER TABLE statements (if
desired) must show integrity constraints, as appropriate, for NOT NULL, UNIQUE, PRIMARY
KEY, FOREIGN KEY, REFERENCES, and CHECK constraints. Be sure to save your SQL script
file used to create these tables with a .sql extension and your output SPOOL file with a .lst or .txt
extension. You should rerun and test your SQL script file until it runs without any errors (this is
why you’ll want to include DROP TABLE statements). Submit your SPOOL file showing that all
SQL in your SQL script file worked properly.

2) Populate each of your tables with at least five valid rows of data each and show the SQL
INSERT statements as you executed them. Populate other tables in your database, as
necessary, to satisfy referential integrity. Save your SQL script file and SPOOL file with the
correct extensions. You should test and rerun your SQL script file until it runs without any errors.
Submit your SPOOL file showing that all SQL in your SQL script file worked properly.

3) Develop an SQL script file to perform the following queries and updates. You should test your
SQL script file until it runs without any errors.

o Retrieve all of your customers’ names, account numbers, and addresses (street and zip
code only), sorted by account number.

o Retrieve all of the videos rented in the last 30 days and sort in chronological rental date
order.

o Produce a list of your distributors and all their information sorted in order by company
name.

o Update a customer name to change their maiden name to a married name. You can
choose which row to update. Make sure that you use the primary key column in your
WHERE clause to affect only a specific row. You may want to include a ROLLBACK
statement to undo your data update.

o Delete a customer from the database. You can choose which row to delete. Make sure
that you use the primary key column in your WHERE clause to affect only a specific row.
You may want to include a ROLLBACK statement to undo your data deletion.

Submit your SPOOL file(s) showing that all SQL in your SQL script file worked properly. Show the actual
SQL statements executed and the results the SQL produced below the code by making sure that you
have a SET ECHO STATEMENT in your SQL script file(s).

Do NOT submit your .sql SQL script files.

Grading rubric

Attribute Meets Does Not Meet Expectations

CREATE TABLE and
ALTER TABLE SQL
statements

30 points
All SQL statements are
syntactically correct and
execute without errors; all
integrity constraints are
properly declared;

0 points
Many SQL statements fail due to syntax
errors or SQL is missing

INSERT SQL statements

25 points
All SQL statements are
syntactically correct and
execute without errors.

0 points
Many SQL statements fail due to syntax
errors and/or integrity constraint violations
or SQL is missing

SELECT SQL statements 20 points
All SQL statements are
syntactically correct and
execute without errors.

0 points
Many SQL statements fail due to syntax
errors or SQL is missing

UPDATE and DELETE
SQL statements

10 points
Statements execute without
errors based on primary key
column in WHERE clause

0 points
Statements fail due to syntax or other errors

SQL script file and
SPOOL file

15 points
Demonstrates full ability to
create and use an Oracle SQL
script file and output SPOOL
file

0 points
Many errors setting up and using an SQL
script file with SPOOL file or no attempt
made at all

CMIS320 Project 4

Examine the following relation and its attributes and answer the following questions. Assume these are
the values for “all time”. Assume girls with the same name are the same person.

GIRL GROUP AGE GAME CATEGORY PRICE

Charlotte 5 year olds 5 Mirror Makeup 4.88

Susan 6 year olds 6 Lipstick Makeup 5.95

Jane 5 year olds 5 Chess Games 7.55

Susan 6 year olds 6 Checkers Games 5.95

Susan 6 year olds 6 Mirror Makeup 4.88

Carrie 6 year olds 6 Lipstick Makeup 5.95

Jacqueline 5 year olds 5 Visual Basic Prog. Languages 199.99

1) Is this relation in at least 1NF? Why or why not?

2) What is the primary key of the initial relation (assume the values shown are the only
possible tuples for all time)? Remember that a primary key must be unique and not
null.

3) Describe the specific data anomalies that exist if we DELETE the tuple containing

Jacqueline.

4) Draw a functional dependency diagram for the initial relation. This diagram should
agree with the primary key you selected in above.

5) Based on your diagram, what normal form is the initial relation in? Why?

6) If necessary, decompose the initial relation into a set of non-loss 3NF relations by

showing the relations, attributes, and tuples. Show complete relations with attribute
headings and all data values in the tuples of your relations. Determine the number of
3NF relations you end up with after normalization, write this number, and then circle
the number.

Grading rubric

Attributes Meets Does Not Meet

Normal form

20 points
Student correctly identifies normal
form of initial relation

0 points
Major error in identification of normal
form or not specified

Primary key

25 points
Student correctly identified primary
key of initial relation

0 points
Major error with identification of
primary key or not specified

Data anomalies 15 points
Student correctly describes data
anomalies

0 points
Major errors with description of data
anomalies or not specified

Functional dependency
diagram

15 points
Student correctly develops functional
dependency diagram of initial
relation

0 points
Major errors developing functional
dependency diagram or not specified

Normalized 3NF
relations

25 points
Student correctly develops the
proper set of 3NF relations via
normalization

0 points
Major errors in development of proper
set of 3NF relations or not specified