Managing Database Environment

dbprojectp1.pdf

Home >Computer Science homework help >Managing Database Environment

ISQA 3310, Managing the Database Environment Project Part 1 – Data Modeling and Entity-Relationship Diagramming

Database Project Part 1: 75 points Due: Wednesday, March 19th 2014 @ 1:30 pm.

Introduction – Data Modeling: This assignment is part 1 of 3 that will lead you through the database development process for a specific application. This portion of the project focuses on data modeling. The project is based on the http://www.movietickets.com/ website, described below. The Movie Tickets project is to develop a database to support a web-site selling tickets to movies around the country. In the ANSI/SPARC 3-schema model of databases, external schemas reflect the view of the data held by an individual user, application, report, form, etc. One source of an external schema could be a web page. For this project, you will develop E-R diagrams reflecting the data requirements of individual web pages, and then integrate these multiple external schemas into a single conceptual schema that can support the entire web application. You will use the ERwin data modeling tool from Computer Associates to complete this project. When creating the model, select Logical/Physical. Select Oracle 11.x as the database type. Set your preferences and notation as outlined in the ERwin tutorial (Section 1. Creation of a basic data model) Please read through the tasks, description of the data modeling scenario, and business rules to make sure you understand what you need to do BEFORE actually starting with the assignment. By the way, you don’t have to worry that you are going to create any web pages. We will be focusing on the data needs only. In other words, you will be creating the database that supports the web pages. The description of the web pages, however, will show you what kind of data you will need in your database. Concentrate on the data needs and not so much on the process. For example, a database will not store a ‘Submit button’ of the application that submits user information to the database. However, the database will store information about the user such as username or e-mail address. You need to separate the data from the application design and process. Tasks:

1. Develop an E-R Diagram representing each web page (1-10, excluding 5a and 5c) using IE (crow’s foot) notation. These will represent your external schemas. Pages 5 and 5b may be combined.

2. Develop a single E-R Diagram representing the conceptual schema. Create this schema by integrating the E-R diagrams representing the external schemas developed in task 1.

3. Using a word processing or spreadsheet program to develop a table describing the attributes of each entity type, like the one shown on p. X. For each attribute in your conceptual schema, show the name of the attribute, the entity type it belongs to, a short description, a data type, and any additional detail defining the domain. Note: you should not include foreign key attributes in this table. Let the relationship notation in the conceptual schema take care of relationships between entities.

Description of data modeling scenario Web page 1: The first page of the application does three things: (1) it prompts the user to (a) pick a movie title for which he wishes to see theater options and (b) enter a zip code or city and state near which he wishes to go to the movies. (2) It lists the top ten movies for the current week, showing the movie name and move's revenues for that week. (3) It allows the user to become a member by clicking “Not a Member? Join Now!”.  Option 1: If the user selects a movie title, he is presented with Page 4.  Option 2: If the user enters a zip code or city/state, he is presented with Page 2.  Option 3: If the user elects to become a member, he is presented with Page 8.

http://www.movietickets.com/

ISQA 3310, Managing the Database Environment Project Part 1 – Data Modeling and Entity-Relationship Diagramming

Web page 2: This page lists all of the theaters located within a given radius of the zip code the user specified. The name and address of the theaters are shown. If the user clicks on a theater, he is presented with Page 3. Web page 3: This page lists all of the movies shown at the chosen theater for the selected day. For each movie, the movies title, duration, rating, and show times are shown. If the user clicks on the name of the movie, she is taken to Page 4. If the user clicks on a show time, she is taken to Page 5. In addition, this page shows information about the selected theater including the theater name, address, phone number and theater features (Handicapped Accessible, Stadium Seating, Digital Sound, etc). Web page 4: This web page shows additional information about the film. The information displayed includes the information shown on page 3 (length of film, MPAA rating, title of film). In addition, it indicates the genre, the release date, the movie stars (multiple) starring in the film, the director(s), the producer(s), the writer(s), and the studio. There is also a synopsis of the movie and a review. Typically, the user would return from page 4 to page 3. The user may also enter a zip code or city/state and the user will be presented with Page 2. Web page 5: Page 5 allows the user to order tickets. The user has the option of purchasing one or more tickets in any of the following categories: adult, senior, and child. This is the ticket type. For each type, the user specifies the number of tickets. The web page also displays the ticket price, the service price, and the total price (ticket + service price). At the top of the page, the user sees the name of the movie, the movie run time, and rating; the theater name, address, and telephone number; the screen number (e.g. 1-6); and the show time (date and time). If the user is a member of the Movie Watcher club, she can enter in her member number (to get special discounts). After entering the required information, the user clicks ‘Continue’ and will be presented with Page 5a. Web page 5a: On page 5a the user is prompted to logon to the system using an e-mail address and password or to create an account. If the user needs to create an account, they are presented with Page 8. After creating an account or logging onto the system the user is presented with Page 5b. Web page 5b: On page 5b, a credit card type, number, and expiration date and billing zip code must be entered. Having entered in the information needed to purchase tickets, the user clicks on 'continue' and is presented with the details of the order, in page 6. Two things are possible here. Either there are enough of the requested tickets left available, or there are an insufficient number of tickets left. In the first case, the user is taken to page 6. In the latter, the user is taken to page 5c, which is the same as page 5, except that there is an error message displayed that indicates the number of tickets that are left for that movie. Web page 6: Page 6 asks for confirmation of the order. Much of the content is the same as on page 5, only now the data is presented to the user. The major difference is that the page shows the total for each type of ticket and the total amount of the order. If the user wants to purchase the tickets, he clicks on 'Purchase Tickets' and is taken to page 7. Web page 7: Page 7 provides a confirmation that the transaction was completed and gives the user a tracking number. If the user clicks on the Finish button, he sees a page indicating that the transaction has been completed and a link to take the user back to the homepage. Web page 8: This page collects information to enable the user to be registered on the web site. The on-line form requests the following information: First name, last name, e-mail address, password (and a text box to type the password a second time), password hint, gender, date of birth and zip code. Also, the user may check (or uncheck) a box that says 'Yes, I would like to receive the MovieTickets.com newsletter' followed by a choice of HTML or text format. An additional box may be checked to receive announcements of new services, products, promotionals, etc. Web page 9: Another page in the registration process is Personalize. On this web page, the user sees a list of movie theaters around the zip code entered on the previous page. The user can check those theaters which are his favorites.

ISQA 3310, Managing the Database Environment Project Part 1 – Data Modeling and Entity-Relationship Diagramming

Web page 10: When a user has logged in, he can click on an Order History tab, which shows the orders placed within the last period of time. For each order placed, the following information is displayed: Movie name, Showtime (date and time), Theater name, number & type of tickets (e.g. 2 ADULT), Total ($ amount of the order), Movie Watcher number, E-mail address, Ticket printing (True/False) to indicate whether or not a physical ticket is sent, Tracking #, and order date (timestamp: date and time). Business Rules

1. To select theaters in the user’s area, allow the user to specify a zip code. Theaters located in the zip codes matching the first 3 digits of the specified zip code should be returned in a search query. We will not be retrieving theaters within a distance radius.

2. Each order relates to exactly one showing. If you wish to purchase tickets to more than one showing, you must place separate orders.

3. Each order has its own unique tracking number 4. Each showing relates to exactly one movie. 5. Each showing involves precisely one movie being shown on one screen in precisely one theater at one particular time.

If the same movie is being shown on two different screens at the same time, this is considered two different showings. 6. A single movie may have many showings at many different times in many different theaters. 7. A movie may have multiple directors, multiple actors & actresses, multiple producers, and multiple writers 8. It is possible for multiple movies to be showing at the same theater at the same time. 9. A user must be a member in order to order tickets on the web site and must provide their user name and credit card

information (card type, number, exp. date, and cardholder name) 10. A member may have several theaters listed as favorites, and each theater may be a favorite to many members.

Figure 1: Example Attributes Table (Task 3)

Attribute Name Entity Type Description Data Type Domain

Cinema_Name Cinema A cinema is a building with a collection of screens that shows movies.

varchar(100)

Cinema_Street Cinema The street name and number or street intersection where the cinema is located

varchar (100)

Cinema_City Cinema City in which the cinema is located varchar (50)

Cinema_State Cinema The state in which the cinema is located varchar (2) One of the 50 recognized state abbreviations

Cinema_Zipcode Cinema The zip code within which the cinema is located

number(5)

To Submit: Turn in your E-R diagrams for tasks 1-2 and your attribute table file for task 3. Please zip multiple files together so that only one .zip file gets uploaded to the Assignment on Blackboard. Homework submission: An electronic copy may be submitted to the “Project Part 1” Assignment via Blackboard by the beginning of class on the due date. If you are working with a partner, only one of you should submit the assignment. Please include both names on the submission. Academic Honesty: If you have a question concerning the assignment, please ask me. If you work alone, don’t share or discuss your assignment with others. If you are working with someone else, you can consult your partner (obviously!) but do not consult other people, except me. Refer to the syllabus for more details on academic honesty.

ISQA 3310, Managing the Database Environment Project Part 1 – Data Modeling and Entity-Relationship Diagramming

Late assignment: Late assignments will receive a penalty of 2% for each 24-period after the due date/time, up to a maximum of five days (in 24-hour periods). Late submissions received after Monday, March 24th at 1:30 pm will not be accepted and will receive a zero. No exceptions. File Security and formatting: You are responsible for submitting a readable copy of the assignment, which is formatted correctly. You are responsible for keeping a back-up copy of your file. Once submitted, please check your Blackboard e-mail regularly in case I need additional information concerning the assignment. This is part one of two large assignments, so start early and use the time you have to finish the assignment.