Problem by cuiliqing

VIEWS: 11 PAGES: 35

									Gregory Laurent
Mark Price
ISQS 3348-001
                                      MG Movies

                              Conceptual Data Model
Problem
MG Movies is a growing company that offers a wide variety of movies to rent. As the
company expands, there is a need to closely maintain each store’s inventory. It is also
necessary to track which movies are currently checked out and who is in possession of
which movie. The company is also looking into expanding to online rentals. At this time,
sales are limited to in-store purchases. A better data system is needed to standardize
inventory control methods.

 We will design a database and applications that will help track inventory and allow
employees to manage it. The database will track: customer information, movie
information, due dates, prices and the number of copies of a movie available. It will be
designed for easy expansion as the company grows and diversifies. It is our goal to
maximize functionality, minimize data redundancy, and create versatility. As a
foundation, the database must be robust and use objects that can be added or altered
to fit the current and changing needs of MG Movies.

The specific functionality for MG’s database is defined by the following queries.

   1. Find any customer with a late fee.
   2. Find a movie by the lead actor.
   3. Find if a store has a given movie.
   4. Find a movie by title.
   5. Find the number of copies available for a given movie.
   6. List the movies a particular customer currently has checked out.
   7. Get the return date for a given movie.
   8. Find a movie by the production company.
   9. Find a customer’s phone number and address.
   10. Look up when an account was created or closed.
   11. Find a store location
   12. Find the price for a given movie.

The database should be scalable and fit the needs of MG Movies. The database should
be able to store at least seven thousand movies and the associated data. Initially, there
should be the capacity to hold at least three thousand accounts with the ability to
expand easily. Database query results should take no longer than five seconds. Also,
security is important because we will be keeping track of personal data for each
account.

Database Analysis
The purpose of this analysis is to interpret the needs of MG Movies and develop a
conceptual data model. The initial needs of the database will be defined through the
                                                                                           1
Gregory Laurent
Mark Price
ISQS 3348-001
business rules. A general model is first needed to build upon as the business rules and
specifications of the database become more specific.

Business Rules
This is a general list MG Movies will have the following requirements:
    A movie must be in stock for a customer to be able to rent it.
    Actors will only be cataloged if he or she has a lead role in a movie.
    Accounts must have at least one customer.
    Each account will be kept on record one year past cancel date.
    A movie must be returned after two weeks of the date out.
    Every account must have a primary member.
    The primary member is the only member who can make changes to the account.
    A production company must produce at least one movie.
    A movie can only be produced by many production companies and a production
       company can have many movies.
    Every store must have at least one movie to rent.
    The late fee starts after the members two week period is over.
    After a customer has paid his or her late fee, the fee will be removed from the
       account.
    The late fee is twenty cents for every day after the due date for each movie the
       customer has out past the due date.
    If the late fee reaches 10 dollars, the movie must be purchased to remove the
       late fee from the account
    A customer can have no more than five movies checked out at a time.
    There can be many writers per movie and a writer can have many movies.

Response Time
MG Movies does not want to keep customers waiting while an associate is searching for
a movie. The query results should take no longer than five seconds.

Conceptual Data Model
An object-oriented data model (OODM) will be used to illustrate the data that will be
captured and the relationships between each object. The OODM will be based upon the
business rules established earlier.

Identification of Object Types
The objects that have been determined for the project are as follows:
    Account- The attributes are: AccountID, LateFee, CreateDate, and CancelDate.
    Customer- The attributes are: CustID, CustName, CustAddress, and
       CustPhone.
    Movie- The attributes are: MovieID, MovieTitle, and MoviePrice.
    Producer- The attributes are: ProdID and ProdName.
    Store- The attributes are: StoreID and StoreLocation.

                                                                                          2
Gregory Laurent
Mark Price
ISQS 3348-001
    Lead Actor- The attributes are: ActorID and ActorName.
    Writer- The attributes of writer are: WriterID and WriterName.
    Rental- The attribute is: RentalID.
    Copy- The attribute is CopyID.

The associated classes with their own attributes are:
    Rents- The attributes are: DateOut, DateIn, and ItemPrice.

Identification of Relationships
There is a 1..n (one to many) relationship between Customer and Rental. There is also
a 1..n relationship between Customer and Account. Rental has an n..m (many to many)
relationship with Copy. Movie has an n..m relationship with Writer, Producer, LeadActor,
Store, and Rentals. Copy also exists as an associate class between Movie and Store.
Associated classes will have the created to deal with the n..m relationships.

Figure 1 MG OODM Conceptual Data Model




                                                                                       3
Gregory Laurent
Mark Price
ISQS 3348-001

                                Relational Data Model
Definition of Attributes
    CustID- This is the primary key of Customer. It will hold the unique customer
       identification number. It will hold up to 7 digits and must be able to hold the
       numbers from 1 to 9999999. It will be formatted as an integer. It is also a foreign
       key in the Rental class.
    CustName- This will hold the customer’s name in the customer class. It is not
       unique and must be able to hold 50 characters. It will be formatted as an
       alphanumeric data type.
    CustAddress- This will hold the customer’s address in the customer class. It is
       not unique and must be able to hold 75 characters. It will be formatted as an
       alphanumeric data type.
    CustPhone- This will hold the customer’s phone number in the customer class. It
       is not unique and must be able to hold domestic phone numbers for the time
       being (10 digits). This will be in varChar format.
    MovieID- This is the primary key of the Movie class. It is a unique movie
       identification number. It will hold no more than 5 digits and hold numbers from 1
       to 99999. It will be formatted as an integer. It is also a foreign key in the following
       classes: Copy, MovieStore, MovieLeadActor, and MovieProducer.
    MoveTitle- This is a non-unique attribute of the Movie class. It must be able to
       hold 35 characters and will be formatted for alphanumeric data.
    MoviePrice- This is also a non-unique attribute of Movie class. It must be 5
       characters, including a decimal, and will be formatted as an money data type.
    ProdID- This is a unique attribute of the Producer class. It is an identification
       number that is associated with a particular producer. This will be formatted as an
       alphanumeric data type. It will be able to hold at least 6 characters. It is also a
       foreign key for the MovieProducer associated class.
    ProdName- This holds the producer’s name. It is not unique and will be
       classified as an alphanumeric data type. It will hold at least 50 characters.
    AccountID- This is a unique identification number that is the primary key for the
       Account class. It will be able to hold 10 characters and be defined as an integer.
       It is a foreign key in the Customer class.
    LateFee- This will keep track of any late fees that a customer can accumulate. It
       will hold 5 characters and be formatted as an integer.
    CreationDate- This will be formatted as a date. It will hold the date on which a
       customer’s account was created. It will not be unique.
    CancelDate- It is the same as CreationDate, but it will hold the date that an
       account was closed.
    ActorID- This is the primary key of the LeadActor class and is a unique
       identification number. It will be no more than 7 digits long. It is also a foreign key
       of the MovieLeadActor associated class.

                                                                                             4
Gregory Laurent
Mark Price
ISQS 3348-001
    ActorName- This will contain the actor’s name. It is not unique. It will be no more
      than 50 characters and be formatted as an alphanumeric data type.
    StoreID- This is the primary key of the Store class. It is a unique identification
      number that will be formatted as an integer. It is also a foreign key in the Copy
      associated class.
    StoreLocation- This will hold each store’s location by address. It will be at most
      75 characters and be formatted as an alphanumeric data type.
    WriterID- This is a unique identification number and is the primary key of the
      Writer class. It is also the foreign key of the Movie class. It will hold up to 5 digits
      and be formatted as an integer.
    WriterName- This is a non-unique entry that contains the writer’s name. It will be
      no more than 50 characters and be formatted as a alphanumeric data type.
    DateOut- This will be formatted like CreationDate, but this will hold the date that
      a movie was checked out.
    DateIn- This will be formatted like CreationDate, but this will hold the date that a
      movie is to be returned on.
    RentalID- This will be formatted as an integer. This will track the rentals by
      customers. This is established because a customer can have a rental with many
      movies. This is a foreign key of CustID.
    ItemPrice- This will be formatted as a money data type. This will be non-unique
      data and hold the price at which the movie was purchased or rented.

Relational Data Model
Below is the relational data model of MG Movies gathered from the conceptual data
model. The primary keys are underlined and the foreign keys are in italics to make them
easily identifiable.

Customer (CustID, AccountID, CustName, CustAddress, CustPhone)
Rental (RentalID, CustID)
Movie (MovieID, MovieTitle, MoviePrice)
Producer (ProdID, ProdName)
Account (AccountID, LateFee, CreationDate, CancelDate)
LeadActor (ActorID, ActorName)
Store (StoreID, StoreLocation)
Writer (WriterID, WriterName)
Rents (RentalID, CopyID, DateOut, DateIn, ItemPrice)
Copy (CopyID, MovieID, StoreID)
MovieLeadActor (MovieID, ActorID)
MovieProducer (MovieID, ProdID)
MovieWriter (MovieID, WriterID)

Normalization of relations
If each attribute only holds one value for each row it is an atomic value and is referred
as a 1NF relation. A 2NF relation assumes that there is no dependency between non-
                                                                                             5
Gregory Laurent
Mark Price
ISQS 3348-001
key attributes and a 3NF assumes there is no transitive relation between primary keys
and non-key attributes. All of the above relations are 1NF as well as 2NF and 3NF.

Validation of Queries
Query 1: Find any customer with a late fee.
         Given: Customer with late fee > 0
         Relations Used:
         Customer (CustID, AccountID, CustName, CustAddress, CustPhone)
         Account (AccountID, LateFee, CreationDate, CancelDate)

Query 2: Find a movie by the lead actor.
         Given: Lead Actor
         Relations Used:
         LeadActor (ActorID, ActorName)
         MovieLeadActor (MovieID, ActorID)
         Movie (MovieID, MovieTitle, MoviePrice)

Query 3: Find if a store has a given movie.
         Given: Movie
         Relations Used:
         Movie (MovieID, MovieTitle, MoviePrice)
         Copy (CopyID, MovieID, StoreID)

Query 4: Find a movie by title.
         Given: Title
         Relations Used:
         Movie (MovieID, MovieTitle, MoviePrice)

Query 5: Find the number of copies available for a given movie.
         Given: Movie
         Relations Used:
         Movie (MovieID, MovieTitle, MoviePrice)
         Copy (CopyID, MovieID, StoreID)
         Rents (RentaltID, MovieID, DateOut, DateIn)

Query 6: List the movies a particular customer currently has checked out.
         Given: Customer
         Relations Used:
         Customer (CustID, AccountID, CustName, CustAddress, CustPhone)
         Rental (RentalID, CustID)
         Rents (RentalID, CopyID, DateOut, DateIn, ItemPrice)
         Copy (CopyID, MovieID, StoreID)
         Movie (MovieID, WriterID, MovieTitle, MoviePrice)


                                                                                        6
Gregory Laurent
Mark Price
ISQS 3348-001
Query 7: Get the return date for a given movie.
          Given: Movie
          Relations Used:
          Movie (MovieID, MovieTitle, MoviePrice)
          Copy (CopyID, MovieID, StoreID)
          Rents (RentalID, CopyID, DateOut, DateIn, ItemPrice)

Query 8: Find a movie by the production company.
         Given: Production Company
         Relations Used:
         Producer (ProdID, ProdName)
         MovieProducer (MovieID, ProdID)
         Movie (MovieID, MovieTitle, MoviePrice)

Query 9: Find a customer's phone number and address.
         Given: Customer
         Relations Used:
         Customer (CustID, AccountID, CustName, CustAddress, CustPhone)

Query 10: Look up when an account was created or closed.
          Given: Customer
          Relations Used:
          Customer (CustID, AccountID, CustName, CustAddress, CustPhone)
          Account (AccountID, LateFee, CreationDate, CancelDate)

Query 11: Find a store location.
          Given: Store
          Relations Used:
          Store (StoreID, StoreLocation)

Query 12: Find the price for a given movie.
          Given: Movie
          Relations Used:
          Movie (MovieID, MovieTitle, MoviePrice)

                            Physical Database Design
Purpose
The purpose is to design the physical characteristics using a specific database platform
and language. This section is based on the logical database design in the previous
section. We will be using SQL Server 2005 for our implementation and to specify the
physical design. The attributes, indexes, and security requirements from the previous
section will be defined according to SQL Server 2005 terminology.



                                                                                       7
Gregory Laurent
Mark Price
ISQS 3348-001
The primary keys are underlined and the foreign keys are italicized. A “yes” or “no” will
indicate whether or not an attribute will be indexed. An asterisk indicates the data that
must be secured. It will be secured using encryption. A “P” denotes a primary key. An
“F” indicates a foreign key. A “B” indicates that it is both a foreign and primary key.

Volume and Usage Analysis

        Account
          Attribute       Data Type (Bytes)        Null       Index    Security
          AccountID           Integer (4)          No        Yes (P)     N/A
           LateFee            Money (8)            Yes          No       N/A
         CreationDate        Datetime (4)          No           No       N/A
         CancelDate          Datetime (4)          Yes          No       N/A

        Customer
           Attribute      Data Type (Bytes)        Null       Index    Security
            CustID            Integer(4)           No        Yes (P)     N/A
          AccountID           Integer (4)          No        Yes (F)      *
          CustName          VarChar (50)           No          Yes       N/A
         CustAddress        VarChar (75)           No           No        *
          CustPhone         VarChar (18)           No           No       N/A

        Rental
          Attribute       Data Type (Bytes)        Null       Index    Security
          RentalID            Integer(4)           No        Yes (P)     N/A
           CustID             Integer (4)          No        Yes (F)     N/A


        Writer
          Attribute       Data Type (Bytes)        Null       Index    Security
           WriterID           Integer(4)           No        Yes (P)     N/A
         WriterName         VarChar (50)           No           No       N/A


        Producer
           Attribute      Data Type (Bytes)        Null       Index    Security
            ProdID            Integer(4)           No        Yes (P)     N/A
          ProdName          VarChar (50)           No           No       N/A


        LeadActor
           Attribute      Data Type (Bytes)        Null       Index    Security
            ActorID           Integer(4)           No        Yes (P)     N/A
          ActorName         VarChar (50)           No           No       N/A

                                                                                            8
Gregory Laurent
Mark Price
ISQS 3348-001
         Store
            Attribute   Data Type (Bytes)         Null       Index    Security
             StoreID        Integer(4)            No        Yes (P)     N/A
          StoreLocation    VarChar(75)            No           No       N/A

        Movie
          Attribute      Data Type (Bytes)        Null       Index    Security
           MovieID           Integer(4)           No        Yes (P)     N/A
          MovieTitle       VarChar (35)           No          Yes       N/A
         MoviePrice          Money (8)            No           No       N/A

        Copy
          Attribute      Data Type (Bytes)        Null       Index    Security
           CopyID            Integer(4)           No        Yes (P)     N/A
          MovieID            Integer(4)           No        Yes (F)     N/A
           StoreID           Integer(4)           No        Yes (F)     N/A

        Rents
          Attribute      Data Type (Bytes)        Null       Index    Security
           RentalID          Integer(4)           No        Yes (B)     N/A
            CopyID           Integer(4)           No        Yes (B)     N/A
          ItemPrice          Money (8)            No           No       N/A
           DateOut          Datetime (4)          No           No       N/A
            DateIn          Datetime (4)          No           No       N/A

        MovieLeadActor
          Attribute  Data Type (Bytes)            Null       Index    Security
          MovieID        Integer(4)               No        Yes (B)     N/A
           ActorID       Integer (4)              No        Yes (B)     N/A

        MovieProducer
          Attribute   Data Type (Bytes)           Null       Index    Security
          MovieID         Integer(4)              No        Yes (B)     N/A
           ProdID         Integer (4)             No        Yes (B)     N/A

        MovieWriter
          Attribute      Data Type (Bytes)        Null       Index    Security
          MovieID            Integer(4)           No        Yes (B)     N/A
          WriterID           Integer (4)          No        Yes (B)     N/A

To compute the estimated database size we will follow a set of guidelines to figure the
size in bytes. These guidelines are:
     Compute the size of each row times the number of estimated rows
                                                                                          9
Gregory Laurent
Mark Price
ISQS 3348-001
    Add an additional 40% of overhead for each index
    Add all table sizes to compute the database size
    Multiply database size by 1.5 to account for unexpected growth

The table shows the size of GM Movies database.
                  Basic Size    Basic Size         Index       Total for     Total Database
                    of One      of a Table       Overhead     Each Table           Size
                      Row
Account                20        20*3000=        60000*.4=       84,000
                                   60,000          24,000
Customer              151       151*9000=       1359000*.8=    2,446,200
                                1,359,000        1,087,200
Rental                  8         8*5000=        40000*.8=       72,000
                                   40,000          32,000
Writer                 54        54*1000=        54,000*.4=      75,600
                                   54,000          21,600
Producer               54        54*1000=        54,000*.4=      75,600
                                   54,000          21,600
LeadActor              54        54*1000=        54,000*.4=      75,600
                                   54,000          21,600
Store                  79         79*10=          790*.4=        1106
                                    790             316
Movie                  47       47*10000=       470000*.8=      846,000
                                  470,000         376,000
Copy                   12       12*10000=       120000*1.2=     264,000
                                  120,000         144,000
Rents                  24       24*10000=       240000*.8=      432,000
                                  240,000         192,000
MovieLeadActor          8        8*10000=        80000*.8=      144,000
                                   80,000          64,000
MovieProducer           8        8*10000=        80000*.8=      144,000
                                   80,000          64,000
MovieWriter             8        8*10000=        80000*.8=      144,000
                                   80,000          64,000
                                                                               4,804,106
                                           Total * Unexpected Growth (1.5)     7,206,159

With the provided information we can estimate that the database will be about 8 MB
(million bytes).




                                                                                           10
Gregory Laurent
Mark Price
ISQS 3348-001
                    Database Design and Implementation
Database Implementation
This will show the SQL code to create and populate the database. SQL Server 2005 will
be use as the platform for implementation. The tables for this project will be located in
the MG_Movies_Laurent database.

SQL Code to Create Tables

use MG_Movies_Laurent
Go

Create Table Account (
AccountID integer Primary Key,
LateFee Money NULL,
CreateDate DateTime NOT NULL,
CancelDate DateTime NULL)

Create Table Customer (
CustID integer Primary Key,
CustName VarChar(50) NOT NULL,
CustAddress VarChar(75) NOT NULL,
CustPhone VarChar(30) NOT NULL,
AccountID integer NOT NULL Constraint FK_AccountID
Foreign Key (AccountID) References Account (AccountID))

Create Table Rental (
RentalID integer Primary Key,
CustID integer NOT NULL,
Constraint CustID Foreign Key (CustID) References Customer (CustID))

Create Table Writer (
WriterID integer Primary Key,
WriterName varChar(50))

Create Table Producer (
ProdID integer Primary Key,
ProdName varChar(50) NOT NULL)

Create Table LeadActor (
ActorID integer Primary Key,
ActorName varChar(50) NOT NULL)

Create Table Store (
StoreID integer Primary Key,
StoreLocation varChar(75) NOT NULL)

Create Table Movie (
MovieID integer Primary Key,
MovieTitle VarChar(35) NOT NULL,
MoviePrice Money NOT NULL)


                                                                                       11
Gregory Laurent
Mark Price
ISQS 3348-001

Create Table Copy (
CopyID integer Primary Key,
MovieID integer NOT NULL,
StoreID integer NOT NULL,
Constraint FK_CMovieID Foreign Key (MovieID) References Movie (MovieID),
Constraint FK_CStoreID Foreign Key (StoreID) References Store (StoreID))

Create Table Rents (
CopyID integer NOT NULL,
RentalID integer NOT NULL,
DateOut DateTime NOT NULL,
DateIn DateTime NULL,
ItemPrice money NOT NULL,
Constraint PK_Rents Primary Key (CopyID, RentalID),
Constraint FK_RCopyID Foreign Key (CopyID) References Copy (CopyID),
Constraint FK_RRentalID Foreign Key (RentalID) References Rental (RentalID))

Create Table MovieLeadActor (
MovieID integer NOT NULL,
ActorID integer NOT NULL,
Constraint PK_MovieLeadActor Primary Key (MovieID, ActorID),
Constraint FK_LMovieID Foreign Key (MovieID) References Movie (MovieID),
Constraint FK_LActorID Foreign Key (ActorID) References LeadActor (ActorID))

Create Table MovieProducer (
MovieID integer NOT NULL,
ProdID integer NOT NULL,
Constraint PK_MovieProducer Primary Key (MovieID, ProdID),
Constraint FK_PMovieID Foreign Key (MovieID) References Movie (MovieID),
Constraint FK_PProdID Foreign Key (ProdID) References Producer (ProdID))

Create Table MovieWriter (
MovieID integer NOT NULL,
WriterID integer NOT NULL,
Constraint PK_WMovieWriter Primary Key (MovieID, WriterID),
Constraint FK_WWriterID Foreign Key (WriterID) References Writer (WriterID),
Constraint FK_WMovieID Foreign Key (MovieID) References Movie (MovieID))
GO

Create   Index   CustNameIndex on Customer (CustName)
Create   Index   MovieTitleIndex on Movie (MovieTitle)
Create   Index   CustIDIndex on Customer (CustID)
Create   Index   RentalIDIndex on Rental (RentalID)
Create   Index   MovieIDIndex on Movie (MovieID)
Create   Index   ProdIDIndex on Producer (ProdID)
Create   Index   AccountIDIndex on Account (AccountID)
Create   Index   ActorIDIndex on LeadActor (ActorID)
Create   Index   StoreIDIndex on Store (StoreID)
Create   Index   WriterIDIndex on Writer (WriterID)
GO




                                                                               12
Gregory Laurent
Mark Price
ISQS 3348-001
Data Population of the MG_Movies_Laurent Database
The population sequence is as follows:
   1. Account
   2. Customer
   3. Rental
   4. Writer
   5. Producer
   6. LeadActor
   7. Store
   8. Movie
   9. Copy
   10. Rents
   11. MovieLeadActor
   12. MovieProducer
   13. MovieWriter

SQL Code to Populate Account
INSERT   INTO   Account   VALUES   (1, 0, 'May 4, 2004', null)
INSERT   INTO   Account   VALUES   (2, 5, 'Feb 20, 2007', null)
INSERT   INTO   Account   VALUES   (3, 0, 'Jan 5, 2000', null)
INSERT   INTO   Account   VALUES   (4, 0, 'Mar 25, 2004', null)
INSERT   INTO   Account   VALUES   (5, 0, 'Apr 12, 2002', null)
INSERT   INTO   Account   VALUES   (6, 12, 'Jan 21, 1999', null)
INSERT   INTO   Account   VALUES   (7, 0, 'Jul, 4 1995', null)
INSERT   INTO   Account   VALUES   (8, 2, 'Dec 25, 2006', null)
INSERT   INTO   Account   VALUES   (9, 0, 'Nov 12, 1998', null)
INSERT   INTO   Account   VALUES   (10, 0, 'Oct 10, 2000', null)

SQL Code to Populate Customer
INSERT INTO Customer VALUES         (1, 'Austin Price', '7400 Park Ridge Amarillo TX,
79119', '5687162889', 1)
INSERT INTO Customer VALUES         (2, 'Mark Price', '5406 B. Kemper Lubbock TX,
79416', '1013991098', 1)
INSERT INTO Customer VALUES         (3, 'George W. Bush', '7444 Main Washinton TX,
79115', '5422726327', 6)
INSERT INTO Customer VALUES         (4, 'Jane Bush', '7444 Main Washinton TX, 79115',
'9055962721', 6)
INSERT INTO Customer VALUES         (5, 'Kyle Bush', '7444 Main Washinton TX, 79115',
'9327580397', 6)
INSERT INTO Customer VALUES         (6, 'Dennis Smith', '5555 Teckla Wayne MI,
79119', '9783503583', 9)
INSERT INTO Customer VALUES         (7, 'Mike Smith', '5555 Teckla Wayne MI, 79119',
'5406882557', 9)
INSERT INTO Customer VALUES         (8, 'Sarah Smith', '5555 Teckla Wayne MI, 79119',
'8256827648', 9)
INSERT INTO Customer VALUES         (9, 'John Smith', '5555 Teckla Wayne MI, 79119',
'3928976597', 9)
INSERT INTO Customer VALUES         (10, 'Megan Mills', '4444 34th Dallas TX, 45985',
'7657674546', 10)


                                                                                       13
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT INTO Customer VALUES (11,         'Lee Masters', '3333 Lane Vega CA, 89119',
'3075575347', 2)
INSERT INTO Customer VALUES (12,         'David Masters', '3333 Lane Vega CA, 89119',
'1720242009', 2)
INSERT INTO Customer VALUES (13,         'Sarah Masters', '3333 Lane Vega CA, 89119',
'9690422493', 2)
INSERT INTO Customer VALUES (14,         'Yoda Masters', '3333 Lane Vega CA, 89119',
'1625455971', 2)
INSERT INTO Customer VALUES (15,         'Greg Laurent', '9561 2nd St. Deadmen NY,
78925', '6925612876', 3)
INSERT INTO Customer VALUES (16,         'Jake Laurent', '9562 2nd St. Deadmen NY,
78925', '3220339660', 3)
INSERT INTO Customer VALUES (17,         'Brad Laurent', '9563 2nd St. Deadmen NY,
78925', '5495193456', 3)
INSERT INTO Customer VALUES (18,         'Emily Laurent', '9564 2nd St. Deadmen NY,
78925', '3862575691', 3)
INSERT INTO Customer VALUES (19,         'Sophia Laurent', '9565 2nd St. Deadmen NY,
78925', '9370114393', 3)
INSERT INTO Customer VALUES (20,         'Billy Graham', '5489 Boost Greensville TX,
85612', '1065955390', 4)
INSERT INTO Customer VALUES (21,         'Nick Graham', '5490 Boost Greensville TX,
85612', '5262417414', 4)
INSERT INTO Customer VALUES (22,         'Jessica Graham', '5491 Boost Greensville
TX, 85612', '4737806606', 4)
INSERT INTO Customer VALUES (23,         'Jessica Simpson', '5297 Neptune Lubbock, TX
74916', '4201077880', 5)
INSERT INTO Customer VALUES (24,         'Ashley Simpson', '5298 Neptune Lubbock, TX
74916', '6383723257', 5)
INSERT INTO Customer VALUES (25,         'Jimmy Simpson', '5299 Neptune Lubbock, TX
74916', '9164342617', 5)
INSERT INTO Customer VALUES (26,         'Homer Simpson', '5300 Neptune Lubbock, TX
74916', '4779607975', 5)
INSERT INTO Customer VALUES (27,         'David Barnhill', '2584 Pluto Happy, MN
89461', '1099330429', 7)
INSERT INTO Customer VALUES (28,         'Jack Barnhill', '2585 Pluto Happy, MN
89461', '7762529839', 7)
INSERT INTO Customer VALUES (29,         'Garth Brooks', '2516 Greg St. Sweetwater,
TX 79541', '2759024345', 8)
INSERT INTO Customer VALUES (30,         'George Brooks', '2517 Greg St. Sweetwater,
TX 79541', '5309794934', 8)

SQL Code to Populate Rental
INSERT   INTO   Rental   VALUES   (1, 2)
INSERT   INTO   Rental   VALUES   (2, 5)
INSERT   INTO   Rental   VALUES   (3, 2)
INSERT   INTO   Rental   VALUES   (4, 5)
INSERT   INTO   Rental   VALUES   (5, 6)
INSERT   INTO   Rental   VALUES   (6, 8)
INSERT   INTO   Rental   VALUES   (7, 9)
INSERT   INTO   Rental   VALUES   (8, 10)
INSERT   INTO   Rental   VALUES   (9, 21)
INSERT   INTO   Rental   VALUES   (10, 22)
INSERT   INTO   Rental   VALUES   (11, 22)
INSERT   INTO   Rental   VALUES   (12, 25)
                                                                                       14
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   Rental   VALUES   (13,   16)
INSERT   INTO   Rental   VALUES   (14,   10)
INSERT   INTO   Rental   VALUES   (15,   1)
INSERT   INTO   Rental   VALUES   (16,   14)
INSERT   INTO   Rental   VALUES   (17,   10)
INSERT   INTO   Rental   VALUES   (18,   2)
INSERT   INTO   Rental   VALUES   (19,   8)
INSERT   INTO   Rental   VALUES   (20,   7)
INSERT   INTO   Rental   VALUES   (21,   6)
INSERT   INTO   Rental   VALUES   (22,   8)
INSERT   INTO   Rental   VALUES   (23,   17)
INSERT   INTO   Rental   VALUES   (24,   19)
INSERT   INTO   Rental   VALUES   (25,   20)
INSERT   INTO   Rental   VALUES   (26,   19)
INSERT   INTO   Rental   VALUES   (27,   18)
INSERT   INTO   Rental   VALUES   (28,   10)
INSERT   INTO   Rental   VALUES   (29,   2)
INSERT   INTO   Rental   VALUES   (30,   1)

SQL Code to Populate Writer
INSERT   INTO   Writer   VALUES   (1, 'David Benioff')
INSERT   INTO   Writer   VALUES   (2, 'Cameron Crowe')
INSERT   INTO   Writer   VALUES   (3, 'Frank Daniel')
INSERT   INTO   Writer   VALUES   (4, 'Delmer Daves')
INSERT   INTO   Writer   VALUES   (5, 'David M. Evans')
INSERT   INTO   Writer   VALUES   (6, 'Chris Folino')
INSERT   INTO   Writer   VALUES   (7, 'Tom W. Blackburn')
INSERT   INTO   Writer   VALUES   (8, 'Travis Beacham')
INSERT   INTO   Writer   VALUES   (9, 'John August')
INSERT   INTO   Writer   VALUES   (10, 'Woody Allen')

SQL Code to Populate Producer
INSERT   INTO   Producer    VALUES    (1, 'Tyler Perry')
INSERT   INTO   Producer    VALUES    (2, 'Gary Kurtz')
INSERT   INTO   Producer    VALUES    (3, 'Tim Burton')
INSERT   INTO   Producer    VALUES    (4, 'Mel Brooks')
INSERT   INTO   Producer    VALUES    (5, 'Robert Watts')
INSERT   INTO   Producer    VALUES    (6, 'Steven Spielberg')
INSERT   INTO   Producer    VALUES    (7, 'Drew Barrymore')
INSERT   INTO   Producer    VALUES    (8, 'George Lucas')
INSERT   INTO   Producer    VALUES    (9, 'Branko Lustig')
INSERT   INTO   Producer    VALUES    (10, 'Rick McCallum')

SQL Code to Populate LeadActor
INSERT   INTO   LeadActor    VALUES    (1,   'Jim Carey')
INSERT   INTO   LeadActor    VALUES    (2,   'John Travolta')
INSERT   INTO   LeadActor    VALUES    (3,   'Kevin Bacon')
INSERT   INTO   LeadActor    VALUES    (4,   'Kurt Russell')
INSERT   INTO   LeadActor    VALUES    (5,   'Liam Neeson')
INSERT   INTO   LeadActor    VALUES    (6,   'Mel Gibson')
INSERT   INTO   LeadActor    VALUES    (7,   'Jodie Foster')
INSERT   INTO   LeadActor    VALUES    (8,   'Julia Roberts')
INSERT   INTO   LeadActor    VALUES    (9,   'Katharine Hepburn')
                                                                    15
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   LeadActor   VALUES   (10,   'Kim Basinger')
INSERT   INTO   LeadActor   VALUES   (11,   'Lauren Bacall')
INSERT   INTO   LeadActor   VALUES   (12,   'Meg Ryan')
INSERT   INTO   LeadActor   VALUES   (13,   'Arnold Schwarzenegger')
INSERT   INTO   LeadActor   VALUES   (14,   'Billy Crystal')
INSERT   INTO   LeadActor   VALUES   (15,   'Brad Pitt')
INSERT   INTO   LeadActor   VALUES   (16,   'Richard Gere')
INSERT   INTO   LeadActor   VALUES   (17,   'Robert De Niro')
INSERT   INTO   LeadActor   VALUES   (18,   'Robin Williams')
INSERT   INTO   LeadActor   VALUES   (19,   'Russell Crowe')
INSERT   INTO   LeadActor   VALUES   (20,   'Demi Moore')

SQL Code to Populate Store
INSERT INTO Store VALUES (1, '2022 4th St. Lubbock TX, 79416')
INSERT INTO Store VALUES (2, '6015 34th St. Lubbock TX, 79416')
INSERT INTO Store VALUES (3, '3044 50th St. Lubbock TX, 79118')

SQL Code to Populate Movie
INSERT   INTO   Movie   VALUES   (1, 'Gladiator', 4.99)
INSERT   INTO   Movie   VALUES   (2, 'Shrek 2', 4.99)
INSERT   INTO   Movie   VALUES   (3, 'Shrek 3', 4.99)
INSERT   INTO   Movie   VALUES   (4, 'Simpsons Movie, The', 4.99)
INSERT   INTO   Movie   VALUES   (5, 'Smokin Aces', 4.99)
INSERT   INTO   Movie   VALUES   (6, 'South Park the Movie', 4.99)
INSERT   INTO   Movie   VALUES   (7, 'Spaceballs', 4.99)
INSERT   INTO   Movie   VALUES   (8, 'Spiderman', 4.99)
INSERT   INTO   Movie   VALUES   (9, 'Spiderman 2', 4.99)
INSERT   INTO   Movie   VALUES   (10, 'Paycheck', 4.99)
INSERT   INTO   Movie   VALUES   (11, 'Perfect Creature', 4.99)
INSERT   INTO   Movie   VALUES   (12, 'Phone Booth', 4.99)
INSERT   INTO   Movie   VALUES   (13, 'Pirates of the Caribbean', 4.99)
INSERT   INTO   Movie   VALUES   (14, 'Pirates of the Caribbean 2', 4.99)
INSERT   INTO   Movie   VALUES   (15, 'Pirates of the Caribbean 3', 4.99)
INSERT   INTO   Movie   VALUES   (16, 'Oceans 13 Part 5', 4.99)
INSERT   INTO   Movie   VALUES   (17, 'Omen, The', 4.99)
INSERT   INTO   Movie   VALUES   (18, 'Open Season', 4.99)
INSERT   INTO   Movie   VALUES   (19, 'Out Cold', 4.99)
INSERT   INTO   Movie   VALUES   (20, 'Material Girls', 4.99)
INSERT   INTO   Movie   VALUES   (21, 'Mean Girls', 4.99)
INSERT   INTO   Movie   VALUES   (22, 'Michael Clayton', 4.99)
INSERT   INTO   Movie   VALUES   (23, 'Mist, The', 4.99)
INSERT   INTO   Movie   VALUES   (24, 'Mr Beans Holiday', 4.99)
INSERT   INTO   Movie   VALUES   (25, 'Mr. Brooks', 4.99)
INSERT   INTO   Movie   VALUES   (26, 'Mr. Woodcock', 4.99)
INSERT   INTO   Movie   VALUES   (27, 'Grudge 2', 4.99)
INSERT   INTO   Movie   VALUES   (28, 'Half Baked', 4.99)
INSERT   INTO   Movie   VALUES   (29, 'Hamiltons, The', 4.99)
INSERT   INTO   Movie   VALUES   (30, 'Happily NEver After', 4.99)
INSERT   INTO   Movie   VALUES   (31, 'Harold and Kumar ', 4.99)
INSERT   INTO   Movie   VALUES   (32, 'High School Musical', 4.99)
INSERT   INTO   Movie   VALUES   (33, 'Evan Almighty', 4.99)
INSERT   INTO   Movie   VALUES   (34, 'Click', 4.99)
INSERT   INTO   Movie   VALUES   (35, 'Contractor, The', 4.99)

                                                                            16
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   Movie    VALUES    (36,   'Constantine', 4.99)
INSERT   INTO   Movie    VALUES    (37,   'Core, The', 4.99)
INSERT   INTO   Movie    VALUES    (38,   'Cradle Part 1, The', 4.99)
INSERT   INTO   Movie    VALUES    (39,   'Cradle Part 2, The', 4.99)
INSERT   INTO   Movie    VALUES    (40,   'Cradle Part 3, The', 4.99)
INSERT   INTO   Movie    VALUES    (41,   'Crash', 4.99)
INSERT   INTO   Movie    VALUES    (42,   '28 Days Later', 4.99)
INSERT   INTO   Movie    VALUES    (43,   '2 Fast 2 Furious', 4.99)
INSERT   INTO   Movie    VALUES    (44,   'Accepted', 4.99)
INSERT   INTO   Movie    VALUES    (45,   'A Cinderella Story', 4.99)
INSERT   INTO   Movie    VALUES    (46,   'A.I. Artificial Intelligence', 4.99)
INSERT   INTO   Movie    VALUES    (47,   'American Gangster', 4.99)
INSERT   INTO   Movie    VALUES    (48,   'American Pie Wedding', 4.99)
INSERT   INTO   Movie    VALUES    (49,   'Anchorman', 4.99)
INSERT   INTO   Movie    VALUES    (50,   'Deja Vu', 4.99)

SQL Code to Populate Copy
INSERT   INTO   Copy    VALUES    (1, 19, 1)
INSERT   INTO   Copy    VALUES    (2, 17, 1)
INSERT   INTO   Copy    VALUES    (3, 29, 2)
INSERT   INTO   Copy    VALUES    (4, 14, 1)
INSERT   INTO   Copy    VALUES    (5, 37, 3)
INSERT   INTO   Copy    VALUES    (6, 50, 2)
INSERT   INTO   Copy    VALUES    (7, 38, 3)
INSERT   INTO   Copy    VALUES    (8, 48, 3)
INSERT   INTO   Copy    VALUES    (9, 18, 1)
INSERT   INTO   Copy    VALUES    (10, 46, 3)
INSERT   INTO   Copy    VALUES    (11, 28, 1)
INSERT   INTO   Copy    VALUES    (12, 39, 2)
INSERT   INTO   Copy    VALUES    (13, 1, 3)
INSERT   INTO   Copy    VALUES    (14, 9, 2)
INSERT   INTO   Copy    VALUES    (15, 16, 1)
INSERT   INTO   Copy    VALUES    (16, 29, 2)
INSERT   INTO   Copy    VALUES    (17, 46, 2)
INSERT   INTO   Copy    VALUES    (18, 30, 3)
INSERT   INTO   Copy    VALUES    (19, 17, 2)
INSERT   INTO   Copy    VALUES    (20, 14, 3)
INSERT   INTO   Copy    VALUES    (21, 50, 3)
INSERT   INTO   Copy    VALUES    (22, 24, 1)
INSERT   INTO   Copy    VALUES    (23, 10, 2)
INSERT   INTO   Copy    VALUES    (24, 13, 3)
INSERT   INTO   Copy    VALUES    (25, 43, 2)
INSERT   INTO   Copy    VALUES    (26, 50, 2)
INSERT   INTO   Copy    VALUES    (27, 24, 2)
INSERT   INTO   Copy    VALUES    (28, 26, 3)
INSERT   INTO   Copy    VALUES    (29, 6, 1)
INSERT   INTO   Copy    VALUES    (30, 27, 3)
INSERT   INTO   Copy    VALUES    (31, 3, 3)
INSERT   INTO   Copy    VALUES    (32, 24, 2)
INSERT   INTO   Copy    VALUES    (33, 43, 3)
INSERT   INTO   Copy    VALUES    (34, 24, 3)
INSERT   INTO   Copy    VALUES    (35, 29, 3)
INSERT   INTO   Copy    VALUES    (36, 7, 1)
INSERT   INTO   Copy    VALUES    (37, 11, 1)
                                                                                  17
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   Copy   VALUES   (38,   39, 1)
INSERT   INTO   Copy   VALUES   (39,   27, 2)
INSERT   INTO   Copy   VALUES   (40,   28, 2)
INSERT   INTO   Copy   VALUES   (41,   24, 3)
INSERT   INTO   Copy   VALUES   (42,   20, 2)
INSERT   INTO   Copy   VALUES   (43,   2, 2)
INSERT   INTO   Copy   VALUES   (44,   11, 2)
INSERT   INTO   Copy   VALUES   (45,   25, 2)
INSERT   INTO   Copy   VALUES   (46,   41, 1)
INSERT   INTO   Copy   VALUES   (47,   4, 1)
INSERT   INTO   Copy   VALUES   (48,   40, 2)
INSERT   INTO   Copy   VALUES   (49,   24, 1)
INSERT   INTO   Copy   VALUES   (50,   37, 3)
INSERT   INTO   Copy   VALUES   (51,   28, 3)
INSERT   INTO   Copy   VALUES   (52,   6, 2)
INSERT   INTO   Copy   VALUES   (53,   11, 2)
INSERT   INTO   Copy   VALUES   (54,   13, 3)
INSERT   INTO   Copy   VALUES   (55,   6, 2)
INSERT   INTO   Copy   VALUES   (56,   27, 3)
INSERT   INTO   Copy   VALUES   (57,   8, 1)
INSERT   INTO   Copy   VALUES   (58,   28, 3)
INSERT   INTO   Copy   VALUES   (59,   34, 1)
INSERT   INTO   Copy   VALUES   (60,   12, 3)
INSERT   INTO   Copy   VALUES   (61,   36, 1)
INSERT   INTO   Copy   VALUES   (62,   15, 1)
INSERT   INTO   Copy   VALUES   (63,   49, 1)
INSERT   INTO   Copy   VALUES   (64,   33, 3)
INSERT   INTO   Copy   VALUES   (65,   23, 1)
INSERT   INTO   Copy   VALUES   (66,   31, 3)
INSERT   INTO   Copy   VALUES   (67,   34, 3)
INSERT   INTO   Copy   VALUES   (68,   44, 3)
INSERT   INTO   Copy   VALUES   (69,   4, 1)
INSERT   INTO   Copy   VALUES   (70,   9, 2)
INSERT   INTO   Copy   VALUES   (71,   25, 1)
INSERT   INTO   Copy   VALUES   (72,   41, 2)
INSERT   INTO   Copy   VALUES   (73,   10, 1)
INSERT   INTO   Copy   VALUES   (74,   20, 2)
INSERT   INTO   Copy   VALUES   (75,   30, 3)
INSERT   INTO   Copy   VALUES   (76,   40, 3)
INSERT   INTO   Copy   VALUES   (77,   29, 1)
INSERT   INTO   Copy   VALUES   (78,   9, 1)
INSERT   INTO   Copy   VALUES   (79,   48, 2)
INSERT   INTO   Copy   VALUES   (80,   18, 2)
INSERT   INTO   Copy   VALUES   (81,   10, 2)
INSERT   INTO   Copy   VALUES   (82,   27, 1)
INSERT   INTO   Copy   VALUES   (83,   33, 1)
INSERT   INTO   Copy   VALUES   (84,   48, 1)
INSERT   INTO   Copy   VALUES   (85,   49, 3)
INSERT   INTO   Copy   VALUES   (86,   4, 1)
INSERT   INTO   Copy   VALUES   (87,   44, 2)
INSERT   INTO   Copy   VALUES   (88,   41, 1)
INSERT   INTO   Copy   VALUES   (89,   41, 1)
INSERT   INTO   Copy   VALUES   (90,   36, 1)
INSERT   INTO   Copy   VALUES   (91,   40, 2)

                                                18
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   Copy   VALUES   (92, 36, 1)
INSERT   INTO   Copy   VALUES   (93, 26, 2)
INSERT   INTO   Copy   VALUES   (94, 45, 1)
INSERT   INTO   Copy   VALUES   (95, 20, 2)
INSERT   INTO   Copy   VALUES   (96, 26, 1)
INSERT   INTO   Copy   VALUES   (97, 35, 2)
INSERT   INTO   Copy   VALUES   (98, 13, 3)
INSERT   INTO   Copy   VALUES   (99, 22, 2)
INSERT   INTO   Copy   VALUES   (100, 4, 3)
INSERT   INTO   Copy   VALUES   (101, 22, 3)
INSERT   INTO   Copy   VALUES   (102, 18, 3)
INSERT   INTO   Copy   VALUES   (103, 14, 2)
INSERT   INTO   Copy   VALUES   (104, 28, 1)
INSERT   INTO   Copy   VALUES   (105, 3, 2)
INSERT   INTO   Copy   VALUES   (106, 44, 2)
INSERT   INTO   Copy   VALUES   (107, 45, 2)
INSERT   INTO   Copy   VALUES   (108, 27, 1)
INSERT   INTO   Copy   VALUES   (109, 3, 1)
INSERT   INTO   Copy   VALUES   (110, 15, 1)
INSERT   INTO   Copy   VALUES   (111, 15, 2)
INSERT   INTO   Copy   VALUES   (112, 37, 3)
INSERT   INTO   Copy   VALUES   (113, 12, 3)
INSERT   INTO   Copy   VALUES   (114, 47, 2)
INSERT   INTO   Copy   VALUES   (115, 25, 1)
INSERT   INTO   Copy   VALUES   (116, 8, 3)
INSERT   INTO   Copy   VALUES   (117, 44, 3)
INSERT   INTO   Copy   VALUES   (118, 50, 2)
INSERT   INTO   Copy   VALUES   (119, 38, 3)
INSERT   INTO   Copy   VALUES   (120, 30, 3)
INSERT   INTO   Copy   VALUES   (121, 39, 1)
INSERT   INTO   Copy   VALUES   (122, 12, 3)
INSERT   INTO   Copy   VALUES   (123, 16, 3)
INSERT   INTO   Copy   VALUES   (124, 26, 1)
INSERT   INTO   Copy   VALUES   (125, 22, 2)
INSERT   INTO   Copy   VALUES   (126, 9, 2)
INSERT   INTO   Copy   VALUES   (127, 50, 3)
INSERT   INTO   Copy   VALUES   (128, 15, 2)
INSERT   INTO   Copy   VALUES   (129, 9, 1)
INSERT   INTO   Copy   VALUES   (130, 22, 2)
INSERT   INTO   Copy   VALUES   (131, 37, 1)
INSERT   INTO   Copy   VALUES   (132, 19, 3)
INSERT   INTO   Copy   VALUES   (133, 36, 1)
INSERT   INTO   Copy   VALUES   (134, 31, 1)
INSERT   INTO   Copy   VALUES   (135, 10, 2)
INSERT   INTO   Copy   VALUES   (136, 37, 3)
INSERT   INTO   Copy   VALUES   (137, 50, 1)
INSERT   INTO   Copy   VALUES   (138, 42, 1)
INSERT   INTO   Copy   VALUES   (139, 18, 1)
INSERT   INTO   Copy   VALUES   (140, 49, 2)
INSERT   INTO   Copy   VALUES   (141, 46, 1)
INSERT   INTO   Copy   VALUES   (142, 48, 3)
INSERT   INTO   Copy   VALUES   (143, 11, 2)
INSERT   INTO   Copy   VALUES   (144, 2, 1)
INSERT   INTO   Copy   VALUES   (145, 38, 1)

                                               19
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   Copy    VALUES   (146,   2, 2)
INSERT   INTO   Copy    VALUES   (147,   43, 3)
INSERT   INTO   Copy    VALUES   (148,   33, 1)
INSERT   INTO   Copy    VALUES   (149,   36, 1)
INSERT   INTO   Copy    VALUES   (150,   42, 2)

SQL Code to Populate Rents
INSERT   INTO   Rents   VALUES    (1, 1, 'Nov 28, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (2, 1, 'Nov 28, 2007', 'Nov 29, 2007', 6.99)
INSERT   INTO   Rents   VALUES    (3, 2, 'Nov 25, 2007', 'Nov 26, 2007', 5.99)
INSERT   INTO   Rents   VALUES    (4, 2, 'Nov 25, 2007', null, 3.99)
INSERT   INTO   Rents   VALUES    (5, 2, 'Nov 25, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (6, 3, 'Nov 30, 2007', null, 3.99)
INSERT   INTO   Rents   VALUES    (7, 4, 'Nov 22, 2007', null, 6.99)
INSERT   INTO   Rents   VALUES    (8, 4, 'Nov 22, 2007', 'Nov 29, 2007', 5.99)
INSERT   INTO   Rents   VALUES    (9, 5, 'Nov 30, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (10, 6, 'Nov 27, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (11, 6, 'Nov 27, 2007', 'Dec 1, 2007', 5.99)
INSERT   INTO   Rents   VALUES    (12, 6, 'Nov 27, 2007', 'Dec 1, 2007', 3.99)
INSERT   INTO   Rents   VALUES    (13, 6, 'Nov 27, 2007', 'Dec 1, 2007', 4.99)
INSERT   INTO   Rents   VALUES    (2, 7, 'Nov 30, 2007', 'Dec 1, 2007', 5.99)
INSERT   INTO   Rents   VALUES    (15, 8, 'Dec 1, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (16, 9, 'Dec 1, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (13, 10, 'Dec 2, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (18, 10, 'Dec 2, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (19, 11, 'Dec 3, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (20, 12, 'Dec 3, 2007', null, 4.99)
INSERT   INTO   Rents   VALUES    (2, 13, 'Dec 4, 2007', null, 4.99)
INSERT   INTO   Rents   VALUES    (22, 13, 'Dec 4, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (23, 14, 'Dec 3, 2007', null, 6.99)
INSERT   INTO   Rents   VALUES    (24, 14, 'Dec 3, 2007', null, 5.99)
INSERT   INTO   Rents   VALUES    (8, 15, 'Dec 4, 2007', null, 5.99)

SQL Code to Populate MovieLeadActor
INSERT   INTO   MovieLeadActor      VALUES   (1, 6)
INSERT   INTO   MovieLeadActor      VALUES   (2, 19)
INSERT   INTO   MovieLeadActor      VALUES   (3, 12)
INSERT   INTO   MovieLeadActor      VALUES   (4, 12)
INSERT   INTO   MovieLeadActor      VALUES   (5, 5)
INSERT   INTO   MovieLeadActor      VALUES   (6, 5)
INSERT   INTO   MovieLeadActor      VALUES   (7, 19)
INSERT   INTO   MovieLeadActor      VALUES   (8, 1)
INSERT   INTO   MovieLeadActor      VALUES   (9, 13)
INSERT   INTO   MovieLeadActor      VALUES   (10, 18)
INSERT   INTO   MovieLeadActor      VALUES   (11, 2)
INSERT   INTO   MovieLeadActor      VALUES   (12, 10)
INSERT   INTO   MovieLeadActor      VALUES   (13, 10)
INSERT   INTO   MovieLeadActor      VALUES   (14, 15)
INSERT   INTO   MovieLeadActor      VALUES   (15, 3)
INSERT   INTO   MovieLeadActor      VALUES   (16, 14)
INSERT   INTO   MovieLeadActor      VALUES   (17, 20)
INSERT   INTO   MovieLeadActor      VALUES   (18, 16)
INSERT   INTO   MovieLeadActor      VALUES   (19, 5)

                                                                                 20
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   MovieLeadActor    VALUES    (20,   9)
INSERT   INTO   MovieLeadActor    VALUES    (21,   4)
INSERT   INTO   MovieLeadActor    VALUES    (22,   7)
INSERT   INTO   MovieLeadActor    VALUES    (23,   2)
INSERT   INTO   MovieLeadActor    VALUES    (24,   17)
INSERT   INTO   MovieLeadActor    VALUES    (25,   7)
INSERT   INTO   MovieLeadActor    VALUES    (26,   3)
INSERT   INTO   MovieLeadActor    VALUES    (27,   16)
INSERT   INTO   MovieLeadActor    VALUES    (28,   14)
INSERT   INTO   MovieLeadActor    VALUES    (29,   19)
INSERT   INTO   MovieLeadActor    VALUES    (30,   8)
INSERT   INTO   MovieLeadActor    VALUES    (31,   10)
INSERT   INTO   MovieLeadActor    VALUES    (32,   19)
INSERT   INTO   MovieLeadActor    VALUES    (33,   19)
INSERT   INTO   MovieLeadActor    VALUES    (34,   12)
INSERT   INTO   MovieLeadActor    VALUES    (35,   20)
INSERT   INTO   MovieLeadActor    VALUES    (36,   6)
INSERT   INTO   MovieLeadActor    VALUES    (37,   15)
INSERT   INTO   MovieLeadActor    VALUES    (38,   13)
INSERT   INTO   MovieLeadActor    VALUES    (39,   12)
INSERT   INTO   MovieLeadActor    VALUES    (40,   4)
INSERT   INTO   MovieLeadActor    VALUES    (41,   14)
INSERT   INTO   MovieLeadActor    VALUES    (42,   3)
INSERT   INTO   MovieLeadActor    VALUES    (43,   7)
INSERT   INTO   MovieLeadActor    VALUES    (44,   1)
INSERT   INTO   MovieLeadActor    VALUES    (45,   14)
INSERT   INTO   MovieLeadActor    VALUES    (46,   20)
INSERT   INTO   MovieLeadActor    VALUES    (47,   8)
INSERT   INTO   MovieLeadActor    VALUES    (48,   13)
INSERT   INTO   MovieLeadActor    VALUES    (49,   16)
INSERT   INTO   MovieLeadActor    VALUES    (50,   6)

SQL Code to Populate MovieProducer
INSERT   INTO   MovieProducer    VALUES    (1, 8)
INSERT   INTO   MovieProducer    VALUES    (2, 2)
INSERT   INTO   MovieProducer    VALUES    (3, 4)
INSERT   INTO   MovieProducer    VALUES    (4, 5)
INSERT   INTO   MovieProducer    VALUES    (5, 3)
INSERT   INTO   MovieProducer    VALUES    (6, 6)
INSERT   INTO   MovieProducer    VALUES    (7, 8)
INSERT   INTO   MovieProducer    VALUES    (8, 2)
INSERT   INTO   MovieProducer    VALUES    (9, 4)
INSERT   INTO   MovieProducer    VALUES    (10, 2)
INSERT   INTO   MovieProducer    VALUES    (11, 8)
INSERT   INTO   MovieProducer    VALUES    (12, 9)
INSERT   INTO   MovieProducer    VALUES    (13, 5)
INSERT   INTO   MovieProducer    VALUES    (14, 10)
INSERT   INTO   MovieProducer    VALUES    (15, 8)
INSERT   INTO   MovieProducer    VALUES    (16, 2)
INSERT   INTO   MovieProducer    VALUES    (17, 8)
INSERT   INTO   MovieProducer    VALUES    (18, 1)
INSERT   INTO   MovieProducer    VALUES    (19, 3)
INSERT   INTO   MovieProducer    VALUES    (20, 3)
INSERT   INTO   MovieProducer    VALUES    (21, 6)
                                                         21
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   MovieProducer   VALUES   (22,   3)
INSERT   INTO   MovieProducer   VALUES   (23,   9)
INSERT   INTO   MovieProducer   VALUES   (24,   9)
INSERT   INTO   MovieProducer   VALUES   (25,   10)
INSERT   INTO   MovieProducer   VALUES   (26,   9)
INSERT   INTO   MovieProducer   VALUES   (27,   7)
INSERT   INTO   MovieProducer   VALUES   (28,   7)
INSERT   INTO   MovieProducer   VALUES   (29,   5)
INSERT   INTO   MovieProducer   VALUES   (30,   6)
INSERT   INTO   MovieProducer   VALUES   (31,   1)
INSERT   INTO   MovieProducer   VALUES   (32,   3)
INSERT   INTO   MovieProducer   VALUES   (33,   8)
INSERT   INTO   MovieProducer   VALUES   (34,   9)
INSERT   INTO   MovieProducer   VALUES   (35,   1)
INSERT   INTO   MovieProducer   VALUES   (36,   4)
INSERT   INTO   MovieProducer   VALUES   (37,   9)
INSERT   INTO   MovieProducer   VALUES   (38,   7)
INSERT   INTO   MovieProducer   VALUES   (39,   3)
INSERT   INTO   MovieProducer   VALUES   (40,   7)
INSERT   INTO   MovieProducer   VALUES   (41,   1)
INSERT   INTO   MovieProducer   VALUES   (42,   9)
INSERT   INTO   MovieProducer   VALUES   (43,   5)
INSERT   INTO   MovieProducer   VALUES   (44,   1)
INSERT   INTO   MovieProducer   VALUES   (45,   8)
INSERT   INTO   MovieProducer   VALUES   (46,   9)
INSERT   INTO   MovieProducer   VALUES   (47,   4)
INSERT   INTO   MovieProducer   VALUES   (48,   3)
INSERT   INTO   MovieProducer   VALUES   (49,   5)
INSERT   INTO   MovieProducer   VALUES   (50,   10)

SQL Code to Populate MovieWriter
INSERT   INTO   MovieWriter   VALUES   (1, 10)
INSERT   INTO   MovieWriter   VALUES   (2, 3)
INSERT   INTO   MovieWriter   VALUES   (3, 9)
INSERT   INTO   MovieWriter   VALUES   (4, 3)
INSERT   INTO   MovieWriter   VALUES   (5, 2)
INSERT   INTO   MovieWriter   VALUES   (6, 2)
INSERT   INTO   MovieWriter   VALUES   (7, 3)
INSERT   INTO   MovieWriter   VALUES   (8, 8)
INSERT   INTO   MovieWriter   VALUES   (9, 7)
INSERT   INTO   MovieWriter   VALUES   (10, 9)
INSERT   INTO   MovieWriter   VALUES   (11, 4)
INSERT   INTO   MovieWriter   VALUES   (12, 1)
INSERT   INTO   MovieWriter   VALUES   (13, 6)
INSERT   INTO   MovieWriter   VALUES   (14, 5)
INSERT   INTO   MovieWriter   VALUES   (15, 9)
INSERT   INTO   MovieWriter   VALUES   (16, 2)
INSERT   INTO   MovieWriter   VALUES   (17, 3)
INSERT   INTO   MovieWriter   VALUES   (18, 3)
INSERT   INTO   MovieWriter   VALUES   (19, 4)
INSERT   INTO   MovieWriter   VALUES   (20, 5)
INSERT   INTO   MovieWriter   VALUES   (21, 7)
INSERT   INTO   MovieWriter   VALUES   (22, 10)
INSERT   INTO   MovieWriter   VALUES   (23, 7)
                                                      22
Gregory Laurent
Mark Price
ISQS 3348-001
INSERT   INTO   MovieWriter   VALUES   (24,   10)
INSERT   INTO   MovieWriter   VALUES   (25,   1)
INSERT   INTO   MovieWriter   VALUES   (26,   4)
INSERT   INTO   MovieWriter   VALUES   (27,   10)
INSERT   INTO   MovieWriter   VALUES   (28,   3)
INSERT   INTO   MovieWriter   VALUES   (29,   5)
INSERT   INTO   MovieWriter   VALUES   (30,   2)
INSERT   INTO   MovieWriter   VALUES   (31,   9)
INSERT   INTO   MovieWriter   VALUES   (32,   9)
INSERT   INTO   MovieWriter   VALUES   (33,   2)
INSERT   INTO   MovieWriter   VALUES   (34,   3)
INSERT   INTO   MovieWriter   VALUES   (35,   3)
INSERT   INTO   MovieWriter   VALUES   (36,   7)
INSERT   INTO   MovieWriter   VALUES   (37,   4)
INSERT   INTO   MovieWriter   VALUES   (38,   6)
INSERT   INTO   MovieWriter   VALUES   (39,   10)
INSERT   INTO   MovieWriter   VALUES   (40,   8)
INSERT   INTO   MovieWriter   VALUES   (41,   2)
INSERT   INTO   MovieWriter   VALUES   (42,   2)
INSERT   INTO   MovieWriter   VALUES   (43,   3)
INSERT   INTO   MovieWriter   VALUES   (44,   7)
INSERT   INTO   MovieWriter   VALUES   (45,   10)
INSERT   INTO   MovieWriter   VALUES   (46,   3)
INSERT   INTO   MovieWriter   VALUES   (47,   8)
INSERT   INTO   MovieWriter   VALUES   (48,   5)
INSERT   INTO   MovieWriter   VALUES   (49,   6)
INSERT   INTO   MovieWriter   VALUES   (50,   8)

Stored Procedures
We will have to create three stored procedures for this project. Each stored procedure
must use at least 2 to 3 tables. The following will show the creation and execution of the
procedures

Query #1 List all movie titles, customer names, and the dates out when given a
date out.

This is the code used to create the procedure.

Create procedure [dbo].[ListDateOut]
@dateout datetime
as
select c.custname, m.movietitle, r.dateout
from rents r join rental rl on r.rentalID=rl.rentalid
join customer c on rl.custid=c.custid
join copy cy on r.copyid=cy.copyid
join movie m on cy.movieid=m.movieid
where r.dateout=@dateout




                                                                                        23
Gregory Laurent
Mark Price
ISQS 3348-001
The output is shown below.




Query #2 Find all movies that a given actor has played in.

This is the code used to create the procedure.

Create procedure [dbo].[ListMoviesActor]
@actor varchar(50)
as
select l.actorname, m.movietitle from movie m
join movieleadactor mla on m.movieid=mla.movieid
join leadactor l on mla.actorid=l.actorid
where l.actorname=@actor


                                                             24
Gregory Laurent
Mark Price
ISQS 3348-001
The result is shown below.




Query #3 List all movie titles, movie identification numbers, copy identification
numbers, the price of the movie, and the price paid when given the movie title.

This is the code used to create the procedure.

Create procedure [dbo].[ListPrices]
@MovieTitle varChar(35)
as
select m.movieid, c.copyid, m.movietitle, m.movieprice, r.itemprice
from copy c join movie m on m.movieid=c.movieid
join rents r on c.copyid=r.copyid
where m.movietitle=@movietitle




                                                                                    25
Gregory Laurent
Mark Price
ISQS 3348-001
The result is shown below.




Visual Basic C# User Functions Application

To make the MG_Movies database more user-friendly we created an application using
Visual Studio 2005. This application has three user functions which allows the operator
to easily execute the stored procedures.




                                                                                      26
Gregory Laurent
Mark Price
ISQS 3348-001
The source code for the main menu is shown below:




                                                    27
Gregory Laurent
Mark Price
ISQS 3348-001
The source code for the ListActor form is shown below:




Here is a more readable version.




                                                         28
Gregory Laurent
Mark Price
ISQS 3348-001
The source code for the Prices form is shown below:




Here is a more readable version.




                                                      29
Gregory Laurent
Mark Price
ISQS 3348-001

The source code for the DateOut form is shown below:




Here is a more readable version.




                                                       30
Gregory Laurent
Mark Price
ISQS 3348-001
Application User Manual

To begin, run the application and a window should appear as shown below:




Find Movies by Actor: If you are wanting to find a list of movies that a given actor stars
in, you would want to click the “Find Movies by Actor” button on the main menu. After
clicking on the button, a new window should popup that looks like the one below:




To run the query you would enter the actors name in the field that follows “actor:”. After
entering in the actor click on the “fill” button and this will output a list of all the movies
that actor plays in.

                                                                                             31
Gregory Laurent
Mark Price
ISQS 3348-001
Find Movies by Date Out: If you are wanting to find all the movies checked out on a
specific date, you would want to click the “Find Movies by Date Out” button on the main
menu. After clicking on the button, a new window should popup that looks like the one
below:




To run the query you would enter the date wanted in the field that follows “dateout:”.
After entering in the date click on the “fill” button and this will output a list of all the
movies checked out on that date.




                                                                                               32
Gregory Laurent
Mark Price
ISQS 3348-001
Find Price Paid by Title: If you are wanting to find how much was paid for a rental in the
past given a specified movie, you would want to click the “Find Price by Title” button on
the main menu. After clicking on the button, a new window should popup that looks like
the one below:




To run the query you would enter the movie title wanted in the field that follows
“MovieTitle:”. After entering in the movie title click on the “fill” button and this will output
the prices paid in the past for that movie.




                                                                                               33
Gregory Laurent
Mark Price
ISQS 3348-001
Exiting: To quit the program you can either exit by clicking the red “x“ in the top right
hand corner of the main menu window or you can click on file->quit which is shown in
the image below:




                                       Conclusion

A good database relies upon good design. By implementing a strong object oriented
data mode, we were able to follow through with creating a strong physical database.
Due to the database’s structure, MG Movies has a reliable, versatile database that
meets the initial expectations of the project. It will allow them to accommodate for
company growth and an expanding inventory. Because the database was designed in
stages, it allowed for a more robust design. Each step provided a crucial tool to test the
strength of the design from the previous section. When it was time to implement the
physical database, the design was highly refined to fit the needs of the company and
prevented the wasted time of having to revise while programming.

In the process of design, we understand the need for business rules and how they
relate to the conceptual data model. It is crucial to illustrate proper relationships
between classes to ensure satisfaction of the necessary queries. Without a strong
conceptual data model, it is impossible to properly make the database the normal form
that is required. Once a good relational data model is created, the attributes can
accurately be defined. This helps the designer to get have more accurate volume
analysis calculation. From this point, the physical database can be implemented
including any stored procedures and functions.

The importance of this project allowed us to understand the process of designing a
database. If the process is flawed, it is much harder for the end result to achieve the
initial goals established by the business rules. The method outlined in this project is not
                                                                                            34
Gregory Laurent
Mark Price
ISQS 3348-001
platform specific. Whether we are working with Oracle or Microsoft SQL, we have been
given a tool that ensures that we continue to generate a quality end result, and that is
the true significance, and strength, of using this technique.




                                                                                       35

								
To top