CMIS 320 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

CMIS 320 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 tablesusing SQL Data Definition Language (DDL) for
each table listedin the metadata of Assignment 1. 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 error. 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

CMIS 320 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.

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

ScholarMatic: Explanation & Answer

Your ready answer from a verified tutor is just a click away for as little as $14.99

---

  

Click Order Now to get 100% Original Answer Customized to your instructions!