Clyde 1 Shenika Clyde Database Management September 8, 2019 Exercise 1 Potential Entities City High schools Teachers Students Degrees Subjects Administrators Clyde 2 Student Subject Phone number Subject number Grade Subject name Age Year introduces Name Grade level Address Teacher Student attendance Age Student number Employee number Subject number Year Grade Year entered the system Year enrolled to subject Employment Teacher attendance Employee number Employee number School name Degree Year entered school Year earned Highest pay rate University name School University School name University name Address Address Year built Year founded Telephone URL Clyde 3 Exercise 2 Verbal Description They should begin with movie entity type. Title is the unique identifier. Year made and the length of the movie are other attributes. On actor type, use the unique actor name as the identifier. Nationality and date of birth can be considered as other attributes. Based on the diagram, VCE must have one DVDs or many for a particular movie type. Each DVD should have a unique serial number and type. In this case, store number should be the unique identifier whereas city, telephone number are also considered important data to keep. Identify customers by their unique customer number. Consider other attributes such as name, number and address. For a customer to be in the database, they must have rented a DVD from VCE store at least one time. Rented and unrented DVDs must be in the database at any given time. Term Project MGMT 4206 – DBMS In this term project, you will apply the skills and knowledge to be learned from this DBMS course to design, develop, and implement a functional Oracle Database system. The skills and knowledge you are going to learn from this course in order to complete this term project is listed below: 1. conceptual database design, 2. logical database design, 3. physical database design 4. Managing database system, and 5. Oracle SQL Phase I: Conceptual database Design You need design and develop an ER diagram based on the requirement description on the next page. Phase II: Logical and physical database design You need to follow the steps listed below. Step 1: Map ER diagram you designed in Part I to Table Instance Chart (Process of Relational Data modeling), Step 2: Populate some sample data into the tables to include the cardinality and participation properties of relationships between any two entities, Step 3: Perform 1st, 2nd, 3rd, and Boyce-Codd normalizations for the tables generating from Step 1 to identify any missing entity (entities), Step 4: Revise your ER diagram from part I to include the missing entity (entities) in it. You need turn in the all the deliverables listed above along with the original ERD from Phase I. Phase III: Implement the database you designed in Phase II into your Oracle DB account by using the SQL to be learned from this course. (These tables in your database should include well-defined structure, sufficient sample data, primary key, and foreign keys). The following is the Requirement statements for a video store: I am the owner of a small video store. We have over 3,000 video tapes that we need to keep track of. Each of our video tapes has a 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 movies. We give each movie a specific id, and then track which movie a tape contains. A tape may be either Beta or VHS format. We always have at least one tape for each movie we track, and each tape is always a copy of a single, specific movie. Our tapes are very long, and we don’t have any movies which require multiple tapes. We are frequently asked for movies starring specific actors. John Wayne and Katherine Hepburn 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 only rent videos 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. “You know we really need to keep a history of all our rentals. Each time a customer rents a tape, we would like to keep the rental date/time and 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.”
Purchase answer to see full attachment