Er modeling - Renita Re'Shondra Dunn(e-Portfolio)



centercenterer modelingMovie DatabaseAbstractThis document uses Oracle Data Modeler to create a SQL Server database based on a set of users’ requirements. Renita R. DunnCIS 4049410077300er modelingMovie DatabaseAbstractThis document uses Oracle Data Modeler to create a SQL Server database based on a set of users’ requirements. Renita R. DunnCIS 404USERS’ REQUIREMENTSTHE MOVIE DATABASE“I’m the owner of a small movie rental store. We have over 3,000 movies that we need to keep track of. Each of our movies has a DVD or VHS tape number. For each movie, we need to know its title and category (e.g., comedy, suspense, drama, action, war, or sci-fi).Yes, we do have multiple copies of many of our movies. We give each movie a specific ID, andthen track which DVD or VHS contains the movie. A movie can be either DVD or VHS format.We always have at least one DVD or VHS tape for each movie we track, and each DVD or VHStape is always a copy of a single, specific movie.Our DVDs and VHS tapes are very long. We don’t have any movies that require multiple DVDs or VHS tapes.""We are frequently asked for movies starring specific actors. John Wayne and Julia Roberts are always popular. So we’d like to keep track of the star actors appearing in each movie. Not all of our movies have star actors. Customers like to know each actor’s “real” birth name and date of birth. We track only actors who appear in the movies in our inventory.We have lots of customers. We rent videos only to people who have joined our 'video club.' To belong to our club, they must have good credit. For each club member, we’d like to keep their first and last name, current phone number, and current address. And, of course, each club member has a membership number.Then we need to keep track of what video tapes each customer currently has checked out. A customer may check out multiple video tapes at any given time. We just track current rentals. We don’t keep track of any rental histories.”We would like to keep the rental date/time and the return date/time. All our tapes are due back the next day, so we don’t need to keep a due date. Keeping this rental history will allow us to analyze the pattern of our rentals. We will be able to determine how many tapes each customer rents and how many times a customer has returned a tape late. We will also know how many times a particular tape has been used and will then know when to retire each tape. We will also be able to analyze our customers’ movie preferences.”Analysis of Users’ RequirementsList of Entities and AttributesMoviesMovieIDTitleCategoryCustomersMembershipIDLast NameFirst NamePhone NumberAddressCreditsCopiesCopyIDTypeRentalRentalIDIssue Return DateActorsActorIDBirth NameScreen NameDOBER Diagram in VISIOER Diagram in Data ModelerThis ER Diagram will result in 7 tables.5 tables one for each every entity2 tables for the two many to many relationshipsThe Relation of Model in Data ModelerDDL for SQL Server 2008-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840-- at: 2014-10-22 10:27:04 CDT-- site: SQL Server 2008-- type: SQL Server 2008CREATE TABLE Actors ( ActorID UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , BirthName UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , ScreenName UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , DOB UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed ) ON "default"GOCREATE TABLE Belong ( Movies_Movies_ID NUMERIC (28) NOT NULL , Copies_Copies_ID NUMERIC (28) NOT NULL , CONSTRAINT Belong__IDX PRIMARY KEY CLUSTERED (Movies_Movies_ID, Copies_Copies_ID)WITH ( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) ON "default" ) ON "default"GOCREATE TABLE Copies ( CopyID UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Type UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Copies_ID NUMERIC (28) NOT NULL IDENTITY NOT FOR REPLICATION , CONSTRAINT Copies_PK PRIMARY KEY CLUSTERED (Copies_ID)WITH ( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) ON "default" ) ON "default"GOCREATE TABLE Customers ( MEMBERSHIP UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Last_Name UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , First_Name UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Phone_Number UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Address UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Credits UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Customers_ID NUMERIC (28) NOT NULL IDENTITY NOT FOR REPLICATION , CONSTRAINT Customers_PK PRIMARY KEY CLUSTERED (Customers_ID)WITH ( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) ON "default" ) ON "default"GOCREATE TABLE Has ( Rental_Rental_ID NUMERIC (28) NOT NULL , Copies_Copies_ID NUMERIC (28) NOT NULL , CONSTRAINT Has__IDX PRIMARY KEY CLUSTERED (Rental_Rental_ID, Copies_Copies_ID)WITH ( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) ON "default" ) ON "default"GOCREATE TABLE Makes ( Customers_Customers_ID NUMERIC (28) NOT NULL , Rental_Rental_ID NUMERIC (28) NOT NULL , CONSTRAINT Makes__IDX PRIMARY KEY CLUSTERED (Customers_Customers_ID, Rental_Rental_ID)WITH ( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) ON "default" ) ON "default"GOCREATE TABLE Movies ( MovieID UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Title UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Category UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Movies_ID NUMERIC (28) NOT NULL IDENTITY NOT FOR REPLICATION , CONSTRAINT Movies_PK PRIMARY KEY CLUSTERED (Movies_ID)WITH ( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) ON "default" ) ON "default"GOCREATE TABLE Rental ( RentalID UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , IssueDate UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , ReturnDate UNKNOWN -- ERROR: Datatype UNKNOWN is not allowed , Rental_ID NUMERIC (28) NOT NULL IDENTITY NOT FOR REPLICATION , CONSTRAINT Rental_PK PRIMARY KEY CLUSTERED (Rental_ID)WITH ( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) ON "default" ) ON "default"GOALTER TABLE BelongADD CONSTRAINT FK_ASS_1 FOREIGN KEY(Movies_Movies_ID)REFERENCES Movies(Movies_ID)ONDELETE NO ACTION ONUPDATE NO ACTIONGOALTER TABLE BelongADD CONSTRAINT FK_ASS_2 FOREIGN KEY(Copies_Copies_ID)REFERENCES Copies(Copies_ID)ONDELETE NO ACTION ONUPDATE NO ACTIONGOALTER TABLE HasADD CONSTRAINT FK_ASS_3 FOREIGN KEY(Rental_Rental_ID)REFERENCES Rental(Rental_ID)ONDELETE NO ACTION ONUPDATE NO ACTIONGOALTER TABLE HasADD CONSTRAINT FK_ASS_4 FOREIGN KEY(Copies_Copies_ID)REFERENCES Copies(Copies_ID)ONDELETE NO ACTION ONUPDATE NO ACTIONGOALTER TABLE MakesADD CONSTRAINT FK_ASS_5 FOREIGN KEY(Customers_Customers_ID)REFERENCES Customers(Customers_ID)ONDELETE NO ACTION ONUPDATE NO ACTIONGOALTER TABLE MakesADD CONSTRAINT FK_ASS_6 FOREIGN KEY(Rental_Rental_ID)REFERENCES Rental(Rental_ID)ONDELETE NO ACTION ONUPDATE NO ACTIONGO-- Oracle SQL Developer Data Modeler Summary Report: -- -- CREATE TABLE 8-- CREATE INDEX 0-- ALTER TABLE 6-- CREATE VIEW 0-- CREATE PACKAGE 0-- CREATE PACKAGE BODY 0-- CREATE PROCEDURE 0-- CREATE FUNCTION 0-- CREATE TRIGGER 0-- ALTER TRIGGER 0-- CREATE DATABASE 0-- CREATE DEFAULT 0-- CREATE INDEX ON VIEW 0-- CREATE ROLLBACK SEGMENT 0-- CREATE ROLE 0-- CREATE RULE 0-- CREATE PARTITION FUNCTION 0-- CREATE PARTITION SCHEME 0-- -- DROP DATABASE 0-- -- ERRORS 18-- WARNINGS 0Database in SQL Server ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download