...1. Write the SQL code to create the table structures for the entities shown in Figure 1. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD. 2. The following tables provide a very small portion of the data that will be kept in the database. This data needs to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in problem 1. 3. Write the SQL command to save the rows inserted in the previous Problem. 4. Write the SQL command to change the movie year for movie number 1245 to 2010. 5. Write the SQL command to change the price code for all Action movies to price code 3. 6. Write a single SQL command to increase all price rental fee values by $0.50. 7. Write the SQL command to save the changes made to the PRICE and MOVIE tables in the previous Problems. 8. Write a query to display the movie title, movie year, and movie genre for all movies (result shown in Figure 2). 9. Write a query to display the movie year, movie title, and movie cost sorted by movie year in descending order (result shown in Figure 3). 10. Write a query to display the movie title, movie year, and movie genre for all movies sorted by movie genre in ascending order, then sorted...
Words: 937 - Pages: 4
...own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Twist in the Wind”. “Twist in the Wind” would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in Figure P7.65. 65. Write the SQL code to create the table structures for the entities shown in Figure P7.65. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD. MEMBERSHIP Mem_ Num Mem_ Fname Mem_ Lname Mem_Street Mem_City Mem_ State Mem_Zip Mem_ Balance RENTAL Rent_Num Rent_Date Mem_Num DETAILRENTAL Rent_Num Vid_Num Detail_Fee Detail_Duedate Detail_Returndate Detail_Dailylatefee VIDEO Vid_Num Vid_Indate Movie_Num MOVIE Movie_Num Movie_Name Movie_Year Movie_Cost Movie_Genre Price_Code PRICE Price_Code Price_Description Price_Rentfee Price_Dailylatefee 68. Write the SQL command to change the movie year for movie number 1245 to 2010. UPDATE Movie SET MOVIE_YEAR = 2010 WHERE MOVIE_NUM = 1245; 69. Write the SQL command to change the price code for...
Words: 344 - Pages: 2
...value in a referenced tuple.Justify your choices. CREATE DATABASE library; CREATE TABLE book( book_id int, title varchar(50), publisher_name varchar(50), primary key(book_id), CONSTRAINT "fk_book_publisher" FOREIGN KEY(publisher_name) REFRENCES publisher(name) ON DELETE SET NULL ON UPDATE CASCADE ); ON DELETE SET NULL is chosen in case the name of a publisher needs to be removed, it can be deleted and set to NULL in all the tables containing publisher’s name ON UPDATE CASCADE is chosen here so that all the tables containing that publisher’s name are updated in accordance to the new publisher’s name CREATE TABLE book_authors( book_id int, author_name varchar(50), CONSTRAINT "fk_book_authors_book" FOREIGN KEY(book_id) REFRENCES book(book_id) ON DELETE CASCADE ON UPDATE CASCADE ); ON DELETE CASCADE is chosen because in case a book is to be removed from the LIBRARY database, all the rows corresponding to that deleted book_id will be deleted from all tables containing book_id. Since this could be dangerous, we could have also used ON DELETE RESTRICT ON UPDATE CASCADE is chosen because in case a book ID has been entered incorrectly, it can be re-entered and the new value of the book_id will be updated in all the tables containing that book_id CREATE TABLE publisher( name varchar(50), address varchar(50), phone bigint, primary key(name) ); CREATE TABLE book_copies( book_id int, branch_id int, no_of_copies int, CONSTRAINT "fk_book_copies_book"...
Words: 1569 - Pages: 7
...Database Design DBM502 – Database Management March 24th, 2014 University of Phoenix Professor: Sam Haidar EzStream This paper will provide an overview of the database to be utilized for the startup company EzStream Inc. The core business of EzStream is to provide a complete solution to stream media content via online or WIFI. Customers will have the choice to rent, buy, or pay a monthly subscription to watch media content. Several components will break down the development of the database and provide details to the database infrastructure. Conceptual Design The conceptual design of EzStream’s DB will consist of Movies, Suppliers, and Customers. Customers will either rent or purchase movies, and have the option of paying a monthly subscription rate to watch movies via digital streaming. Data Analysis and Requirements * Tasks during Research and Analysis: * Identify essential "real world" information (e.g. interviews) * Remove redundant, unimportant details * Clarify unclear natural language statements * Fill remaining gaps in discussions * Distinguish data and operations Requirement Analysis First step: filter essential information vs unimportant details * Essentials * There are customers, suppliers, and media content * Identify age of audience for rentals * Customers have a customer identification number * Four weeks maximal rental time. * Unimportant details * "...Rentals since a few...
Words: 1876 - Pages: 8
...Tiny Video is a small movie rental company with a single store. Tiny Video needs a database system to track the rental of movies to its members. Tiny Video can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie ―Twist in the Wind.‖ ―Twist in the Wind‖ would be one MOVIE, and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime; therefore, there is a M: N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in Figure P1.1. Figure P1.1 Tiny Video ERD Write the SQL code to create the table structures for the entities shown in Figure P1.1. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD. ****d on the referential integrity constraints, you should be able to identify a correct sequence in which to create the tables. The key point is that due to referential integrity constraints, the table contributing its PK as a FK must be created before the related table containing the FK. Database Schema Definition, Basic Constraints, and Queries Data Definition, Constraints, and Schema Changes CREATE TABLE -Specifies a new base relation by...
Words: 4074 - Pages: 17
...Blackboard. EliteVideo is a startup company providing concierge DVD kiosk service in upscale neighborhoods. EliteVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Twist in the Wind”. “Twist in the Wind” would be one MOVIE and each copy would be a VIDEO. A rental transaction (RENTAL) involves one or more videos being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in Figure P7.65. Figure P7.65 EliteVideo ERD [pic] 65. Write the SQL code to create the table structures for the entities shown in Figure P7.65. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD. CREATE TABLE PRICE ( PRICE_CODE INTEGER NOT NULL UNIQUE, PRICE_DESCRIPTION CHAR(15) NOT NULL, PRICE_RENTFEE VARCHAR(3) NOT NULL, PRICE_DAILYLATEFEE VARCHAR(3) NOT NULL, PRIMARY KEY (PRICE_CODE)); CREATE TABLE MOVIE ( MOVIE_NUM INTEGER NOT NULL UNIQUE, MOVIE_TITLE VARCHAR(35) NOT NULL, MOVIE_YEAR INTEGER NOT NULL, MOVIE_COST NUMBER NOT...
Words: 2762 - Pages: 12
...CS 2203 Unit 1 – 8 Self Quiz Review Questions The main purpose of the information model is to inform software developers and provide protocol-specific constructs. Select one: True False Question 2 Which term describes each two-dimensional table or file in the relational model? Select one: a. Database b. Relational Database c. Data Warehouse d. None of the Above Question 3 If a relation has more than one candidate key the one chosen to represent the relation is called the: Select one: a. primary key b. foreign key c. alternate key d. candidate key Question 4 The first hierarchial DBMS was ____ and was released by IBM in 1968? Select one: a. IMS (Information Management System) b. SQL c. DB2 d. Oracle Question 5 What is a field that uniquely describes each record? Select one: a. Composite Key b. Foreign Key c. Primary Key d. None of the Above Question 6 The number of attributes in a relation is known as: Select one: a. The relation degree b. The relation cardinality c. The relation domain d. The relation schema Question 7 Which of the following is NOT an information model? Select one: a. pureXML model b. Relational Model c. Hierarchial model d. Network model Question 8 The network model (CODASYL) was released in what year? Select one: a. 1979 b. 1969 c. 1964 d. 1980 Question 9 A formal description of all the database relations and all of the relationships existing between them...
Words: 4344 - Pages: 18
...construction, utilities, and education are successfully using big data by exploiting meaningful information from all the data they have and using that information in formulating their strategic moves. The Volvo Car Corporation (VCC) is the well-known auto manufacturer founded in 1927 in Gothenburg, Sweden. The Volvo Car Corporation drives product design, quality, cost reduction, and customer satisfaction through data-driven decision-making. The aggregate data volume is large and growing rapidly. In keeping with then-prevailing standards for IT architecture, the company originally began collecting this data in a dedicated data mart. The Volvo Car Corporation wanted to create an effective marketing campaign to tie in with the popular Twilight movie franchise and to create an interactive game would connect to global audience. The idea for the game was that users could play to win a new Volvo XC60 car. In an effort to address its needs for a cost-effective infrastructure that provided the ability to quickly scale up to handle a high volume of traffic and that would also serve a global audience with high performance, Volvo chose to implement a cloud-based solution. After surveying the background of cloud services providers, LBi recommended that Volvo use Windows Azure cloud computing which offers software...
Words: 1273 - Pages: 6
...Online Movie theatre’s Ticket booking system Objective: • This is a online web site on which user as well as theatre owner register themselves and use this site to update movies in theatre and search for particular location of theatre as well as book tickets for particular movie. Also theatre authority can check by ticket number for valid user. Database Design: ER-Schema: FirstName UserID CreditInfo Name Movie Ticket ZipCode StreetAddre f s TheatreID NameofMovie Actor Movie City State Location 1-n Lastna me UserID UserInfo ExpDate Favorite Answer NameofTheatre Theatre NumofScreen TheatreID County MovieID MvoieID Director Screen n-1 1 n TheatreID Capacity Actress Rated Releas eYear Time1 n-n n-n n-n ScreenID ShowTime TheatreID ScreenID Capacity MovieID Tables: CreditInfo: Attributes NumofCreditCard Type Name UserID ExpDate Ticket Movie UserInfo: Attributes FirstName LastName UserID Password Favorite Reply Data Type Text Text Text Text Text Text Data Type Number Text Text Text Text Number Data Type Text Number Text Text Text Number Text Data Type Number Number Number Number Data Type Number Number Text Data Type Number Data Type Number Text Text Text Date Number Text Location Attributes ZipCode State City StreetAddress County TheatreID Movie: Attributes NameOfMovie MovieID Actor Actress Director ReleaseYear Rated Screen: Attributes MovieID ScreenID TheatreID Capacity Theatre: Attributes TheatreID NumofScreen NameOf Theatre ...
Words: 1328 - Pages: 6
...database textbook Elmasri and Navathe, Fundamentals of Database Systems, 6th Edition, Addison-Wesley, 2010. It provides supplemental materials to enhance the practical coverage of concepts in an introductory database systems course. The material presented in this laboratory manual complement many of the chapters of the Elmasri/Navathe text typically covered in most introductory database systems courses. Chapter Mappings The laboratory manual consists of 8 chapters and the following table shows the mapping to the chapters in the Elmasri/Navathe textbook: Laboratory Manual Chapter Elmasri/Navathe 6th Edition Chapter(s) Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapters 7, 8, and 9 Chapters 3, 6, and 26 Chapters 4, 5, and 13 Chapters 4, 5, and 14 Chapters 15 and 16 Chapter 11 Chapter 12 Chapters 13 and 14 Chapter 1 presents ERWin, a popular data modeling software that allows database designers to represent Entity-Relationship diagrams and automatically generate relational SQL code to create the database in one of several commercial relational database management systems such as Oracle or Microsoft SQLServer. The material presented in this chapter is tutorial in nature and covers the COMPANY database design of the Elmasri/Navathe text in detail. Chapter 2 presents three interpreters that can be used to execute queries in Relational Algebra, Domain Relational Calculus, and Datalog. These interpreters are part of a Java package that includes a rudimentary...
Words: 18417 - Pages: 74
...A GUIDE TO SQL Eighth Edition This page intentionally left blank A G U I D E TO S Q L Eighth Edition Philip J. Pratt Grand Valley State University Mary Z. Last University of Mary Hardin-Baylor Australia • Brazil • Japan • Korea • Mexico • Singapore • Spain • United Kingdom • United States A Guide to SQL, Eighth Edition Philip J. Pratt, Mary Z. Last Vice President, Publisher: Jack Calhoun Editor-in-Chief: Alex von Rosenberg Senior Acquisitions Editor: Charles McCormick, Jr. Product Manager: Kate Hennessy Development Editor: Jessica Evans Editorial Assistant: Bryn Lathrop Marketing Director: Brian Joyner Marketing Manager: Bryant Chrzan Marketing Communications Manager: Libby Shipp Marketing Coordinator: Suellen Ruttkay Content Project Manager: Matt Hutchinson Art Director: Stacy Jenkins Shirley, Marissa Falco Cover Designer: Joseph Sherman Cover Image: Getty Images/Taxi/Chris Bell Manufacturing Coordinator: Denise Powers © 2009 Course Technology, Cengage Learning ALL RIGHTS RESERVED. No part of this work covered by the copyright hereon may be reproduced, transmitted, stored, or used in any form or by any means graphic, electronic, or mechanical, including but not limited to photocopying, recording, scanning, digitizing, taping, Web distribution, information networks, or information storage and retrieval systems, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the publisher....
Words: 48772 - Pages: 196
...PL/SQL Chapter 3 Solutions Review Questions 1. C 2. D 3. B 4. A 5. C 6. C 7. A, C 8. A 9. A 10. A 11. Variables are named memory areas that hold values to allow retrieval and manipulation of values within our programs 12. A SELECT statement within a PL/SQL block must include an INTO clause to indicate the variable/s that will hold the data being retrieved. The INTO clause follows the SELECT clause and precedes the FROM clause. In addition, a SELECT statement that returns no rows will raise an Oracle error. 13. The %TYPE attribute is used to declare the data type of a variable based on the data type of a database table column. The data type declaration must include the table and column name in the form: tablename.columnname%TYPE. 14. The %ROWTYPE attribute is used to declare the data type of a record variable based on the row structure of a table. The data type declaration must include the table name in the form: tablename%ROWTYPE. 15. Composite data types allow the creation of a variable that can hold multiple values with various data types as a single unit. Examples include a record and a table of records. Advanced Review Questions 1. B 2. C 3. D 4. A 5. B Hands-On Assignments Assignment 3-1 VARIABLE g_basket NUMBER BEGIN :g_basket := 3; END; / DECLARE lv_ship_date bb_basketstatus.dtstage%TYPE; lv_shipper_txt bb_basketstatus.shipper%TYPE; lv_ship_num bb_basketstatus.shippingnum%TYPE; BEGIN SELECT dtstage...
Words: 920 - Pages: 4
...FLIX2YOU PROPOSAL Illuminated Communications IST210 SEC002 Table of Contents Executive Summary 3 Scope of Work 4 Project Management 5 Project Resources and Budget 8 User Analysis 10 Organization of Data 14 Database Administration 17 Database Backup and Recovery 19 Legal Issues 22 Works Cited 25 Appendix A – DDL Script 26 Appendix B – Sample Reporting 37 Executive Summary Illuminated Communications offers this solution to meet the growing demands of Flix2You. We understand the limitations of the existing database design and have proposed a new design that will provide Flix2You a more robust database environment intended to capture more customer data. This will address the primary concerns of Flix2You and allow for the understanding of its customer’s habits that it seeks to gain. Included in this document you will find our scope of work, as it has been determined based on the requirements that have been provided. With that we developed a project plan highlight the major milestones from project start to final sign off. Please note that Illuminated Communications will provide comprehensive testing, training, and support past go live to ensure our solution meet the demands of Flix2You. Understanding Flix2You users is important to our design process. We have included an in-depth user analysis to help us build our system to support the needs of all Flix2You user from upper management personnel to database administrators. Illuminated Communications...
Words: 5807 - Pages: 24
...Fill in the blanks 1. Consider the database formed by the following tables: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) 1) List full details of all hotels. SELECT ______________ FROM hotel; 2) List full details of all hotels in London. SELECT * FROM hotel WHERE address ______________; 3) List the names and addresses of all guests in London, alphabetically ordered by name. SELECT ______________, address FROM guest WHERE address LIKE ‘%London’ ______________; 4) List all double or family rooms with a price below 40.00 per night, in ascending order of price. SELECT * FROM room WHERE ______________ AND ______________ ______________; 5) List the bookings for which no date_to has been specified. SELECT * FROM booking WHERE dateTo ______________; 6) How many hotels are there? SELECT ______________FROM hotel; 7) What is the average price of a room? SELECT ______________FROM room; 8) What is the total revenue per night from all double rooms? SELECT ______________FROM room WHERE ______________; 2. Consider the following schema definitions: Branch (branchNo, street, city, postcode) Staff (staffNo, fName,lName, position, sex, DOB, salary, branchNo) PropertyforRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo,...
Words: 934 - Pages: 4
...This page intentionally left blank Te n t h E d i t i o n MODERN DATABASE MANAGEMENT Editorial Director: Sally Yagan Editor in Chief: Eric Svendsen Executive Editor: Bob Horan Editorial Project Manager: Kelly Loftus Editorial Assistant: Jason Calcano Director of Marketing: Patrice Lumumba Jones Marketing Manager: Anne Fahlgren Marketing Assistant: Melinda Jensen Senior Managing Editor: Judy Leale Project Manager: Becca Richter Senior Operations Supervisor: Arnold Vila Operations Specialist: Ilene Kahn Senior Art Director: Jayne Conte Cover Designer: Suzanne Behnke Cover Art: Fotolia © vuifah Manager, Visual Research: Karen Sanatar Permissions Project Manager: Shannon Barbe Media Project Manager, Editorial: Denise Vaughn Media Project Manager, Production: Lisa Rinaldi Supplements Editor: Kelly Loftus Full-Service Project Management: PreMediaGlobal Composition: PreMediaGlobal Printer/Binder: Edwards Brothers Cover Printer: Lehigh-Phoenix Color/Hagerstown Text Font: Palatino Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on appropriate page within text. Microsoft® and Windows® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. Screen shots and icons reprinted with permission from the Microsoft Corporation. This book is not sponsored or endorsed by or affiliated with the Microsoft Corporation. Copyright © 2011, 2009, 2007, 2005, 2002...
Words: 193467 - Pages: 774
