Sql Movie Table

In: Computers and Technology

Submitted By anishreddy506
Words 1649
Pages 7
TinyVideo is a small movie rental company with a single store. TinyVideo needs a database system to track the rental of movies to its members. TinyVideo 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 TinyVideo ERD

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.

When your tables are created, accomplish the following:

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 PRICE table. The rest of the insert statements are provided to copy and paste into a script.

MEMBERSHIP
Mem_
Num Mem_
Fname Mem_
Lname Mem_Street Mem_City Mem_
State Mem_Zip Mem_
Balance
102 Tami Dawson 2632 Takli Circle Norene TN 37136 11
103 Curt Knight 4025 Cornell Court Flatgap KY 41219 6
104 Jamal Melendez 788 East 145th Avenue Quebeck TN 38579 0
105 Iva Mcclain 6045 Musket Ball Circle Summit KY 42783 15
106 Miranda Parks 4469 Maxwell Place Germantown TN 38183 0
107 Rosario Elliott 7578 Danner Avenue Columbia…...

Similar Documents

Sql for Beginners

...An Training Guide SQL & SQL*Plus for Beginners Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Introduction Copyright © 1999 – 2004 ApplTop Solutions Limited 2 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding/awareness of the following... • Oracle SQL • Oracle SQL*Plus Copyright © 1999 – 2004 ApplTop Solutions Limited 3 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Course Objectives This course is designed to give each delegate a basic understanding of the following topics... • Oracle SQL. You will learn how to use the industry standard tool for working with a Relational Database. You will also learn many other things, from constructing simple queries to creating your own tables. • Oracle SQL*Plus. After the course you should have a good understanding of SQL*Plus, from starting it, to creating simple reports on it. During the course there will be several exercises to complete. Copyright © 1999 – 2004 ApplTop Solutions Limited 4 Training Guide SQL & SQL*Plus for Beginners www.appltop.com info@appltop.com Note that this guide is meant only as an introduction to SQL and SQL*Plus and therefore much of the newer, more advanced features available in Oracle databases 8i, 9i and 10g are not covered. Copyright © 1999 – 2004 ApplTop......

Words: 19188 - Pages: 77

Database - Sql

...the relational database model most commonly used today? What impact does it have on data integrity? * The idea came from Edgar F Codd in 1970 he worked for IBM at the time * Advantage is the tables define the relationships among themselves by mean of repeating an attribute or column from one table in another table “called Keys” * One of the problems was data redundancy; means to store the same data in more than place in the database * Codd’s theoretical design minimized redundancy * The impact is; the complexity of the design, its easy to create a hard to use database, or hard to fine the data you entered into the database. So a well-designed database = data integrity and flexibility. 3. Distinguish between data integrity and redundancy. * Data integrity = to the accuracy and quality of the data * Redundancy = refers to data that is repeated in multiple places in a database. 4. What is an entity? * An object of concern to a database, such as a customer or sale. Used in the logical design phase of a database. The main key that starts the gathering of data into one place. 5. What are attributes? * A quality that describes or defines some aspect of a database entity. Attributes often correspond to the columns in the tables created in the physical design process. * They are connecting data that relates to the entity data. 6. What is the domain of the database? * Is what the database is about or the......

Words: 2656 - Pages: 11

Sql Tutorial

...SQL Tutorial SQL TUTORIAL Simply Easy Learning by tutorialspoint.com tutorialspoint.com i ABOUT THE TUTORIAL SQL Tutorial SQL is a database computer language designed for the retrieval and management of data in relational database. SQL stands for Structured Query Language. This tutorial will give you quick start with SQL. Audience This reference has been prepared for the beginners to help them understand the basic to advanced concepts related to SQL languages. Prerequisites Before you start doing practice with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. Copyright & Disclaimer Notice All the content and graphics on this tutorial are the property of tutorialspoint.com. Any content from tutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape, or form without the written permission of tutorialspoint.com. Failure to do so is a violation of copyright laws. This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the accuracy of the site or its contents including this tutorial. If you discover that the tutorialspoint.com site or this tutorial content contains some errors, please contact us at webmaster@tutorialspoint.com TUTORIALS POINT Simply Easy Learning Table of Content SQL Tutorial .................................................................

Words: 39505 - Pages: 159

Sql Case

...MGS 8020 Business Intelligence Assignment 3 Please use the SQL (in MS Word) to answer the following questions.  You may use subquery but all questions need to be completed in ONE SQL.  You need to use the following tables (STUDENT and GPA) to answer Q1 – Q4.        [pic]     1. What is the output if you have the following SQL? (10 points) Select * from STUDENT; Uncondition SQL that lists all the students from the student table with the respective columns. Select * from Student$ 2. Please find the average GPA for each student who lives in the following locations: (40 points) |      Atlanta, GA  | United States | |      New Orleans, LA |United States | |      Seoul  | Korea |       is the CITY in Korea Wasn’t sure which way the question was asking for the average GPA so I did both.  Average by the city SELECT Student$.City, AVG(GPA$.GPA) as 'City GPA' FROM GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID WHERE Student$.City IN ('Atlanta','New Orleans','Seoul') group by student$.city Average for the student, who lives in those cities SELECT student$.Student_Name, AVG(GPA$.GPA) FROM GPA$ INNER JOIN Student$ ON GPA$.Student_ID = Student$.Student_ID WHERE Student$.City IN ('Atlanta','New Orleans','Seoul') group by student$.Student_Name 3. Please find the student......

Words: 438 - Pages: 2

Pl/Sql

...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......

Words: 920 - Pages: 4

Sql Server

...SQL Server 2012 Proposal for ABC Inc Name Institution Table of contents Cover page 1 Table of contents 2 Introduction 3 Statement of the problem 3 Solution 4 Conclusion 5 References 6 SQL Server 2012 Proposal for ABC Inc Introduction For an organization to carry out its processes effectively, it is important to have a database. Database management is essential in every organization. There are several methods that have existed over the years. They include excel spreadsheet and Access. These are good database, but I would like to propose a more advanced method. The SQL 2012 is a database system that will enable the organization to utilize technology (Mistry & Misner, 2012). The Microsoft SQL server 2012 consists of several editions that can help ABC Inc to meet its database requirements. Investing in this database system will be of many benefits to the company. Statement of the Problem In the world of changing technology, it is important to adopt change and advanced methods of running a company. Over the years, ABC Inc has been using excel spreadsheets and access; it is time to move to SQL 2012 server. It is not easy to store a lot of data in Excel and access compared to the SQL. The company has been able to use Excel over time, but it has not been possible to get high quality data.......

Words: 943 - Pages: 4

Sql Book

... 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

Table

...University of Phoenix Material Website Review Find national, state, and local governmental health agency websites that address the issue you selected in your Week One assignment. Follow the example to document your findings in the table. Health issue: _Teen Pregnancy__________________________ |Website |Website Information Type |Type of Surveillance: |Data changes over time: |Identify two interventions | | | |survey, self report, |Has the incidence |that affect the issue. | | | |statistics, case report, |increased or decreased? | | | | |and so forth | | | |Example: http://www.cdc.gov/media/h1n1flu/index.htm#FAS |Table of contents, search, |Statistics and |Swine flu incidences have |Vaccine, hand washing, and | | |print, videos, surveillance |self-reporting |decreased. There are now |isolation masks ......

Words: 295 - Pages: 2

Sql Fundamentals

...SQL Fundamentals The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it's really not all that bad. In a series of articles over the next few weeks we'll explore the inner workings of SQL together. By the time we're through, you'll have the fundamental knowledge you need to go out there and start working with databases! This week, our first article in the SQL series provides an introduction to the basic concepts behind SQL and we'll take a brief look at some of the main commands used to create and modify databases. Throughout this article, please keep our goal in mind: we're trying to get the "big picture" of SQL -- not a mastery of the individual commands. We'll provide a few examples for illustrative purposes and explain the theory behind them, but don't feel frustrated if you can't write your own SQL commands after reading this article. We'll cover each of the major commands in detail in future weekly installments. If you'd like a reminder in your e-mail inbox each week when the next article is posted, please take a moment and subscribe to our newsletter. By the way, the correct pronunciation of SQL is a contentious issue within the database community. In their SQL standard, the American National Standards Institute declared that the......

Words: 1859 - Pages: 8

Sql Quiz

... This attempt took 36 minutes.   Question 1 1 / 1 pts Parameters for stored procedures and functions can be of any valid SQL Server data type except Parameters for stored procedures and functions can be of any valid SQL Server data type except   date/time Correct!   table   xml   numeric   Question 2 0 / 1 pts Skip to question text. Code a statement that calls the following stored procedure and passes the values ‘2011-12-01’ and 122 to its input parameters. Pass the parameters by name in the reverse order that they’re declared in the procedure. CREATE PROC spInvoiceTotal1        @DateVar smalldatetime,        @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar; ______________________________________________________________________________ You Answered Correct Answers EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01'; EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2011-12-01'   Question 3 0 / 1 pts Before you can pass a table to a stored procedure or a function as a parameter, you must use the ______________________ statement to create a user-defined table type. Before you can pass a table to a stored procedure or a function as a parameter, you must use the ______________________ statement to create a user-defined table type. You Answered Correct Answers CREATE TYPE   Question 4 1 / 1 pts Unless a database system supports......

Words: 645 - Pages: 3

Tables

...Appendix B: Tables TABLE 1 CUMULATIVE PROBABILITIES FOR THE STANDARD NORMAL DISTRIBUTION Entries in the table give the area under the curve to the left of the z value. For example, for z = –.85, the cumulative probability is......

Words: 8439 - Pages: 34

Joins in Sql

...Join in SQL SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. SQL Join is used for combining column from two or more tables by using values common to both tables. JoinKeyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is(n-1) where n, is number of tables. A table can also join to itself known as, Self Join. Types of Join The following are the types of JOIN that we can use in SQL. * Inner * Outer * Left * Right Cross JOIN or Cartesian Product This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table. Cross JOIN Syntax is, SELECT column-name-list from table-name1 CROSS JOIN table-name2; Example of Cross JOIN The class table, ID | NAME | 1 | abhi | 2 | adam | 4 | alex | The class_info table, ID | Address | 1 | DELHI | 2 | MUMBAI | 3 | CHENNAI | Cross JOIN query will be, SELECT * from class, cross JOIN class_info; The result table will look like, ID | NAME | ID | Address | 1 | abhi | 1 | DELHI | 2 | adam | 1 | DELHI | 4 | alex | 1 | DELHI | 1 | abhi | 2 | MUMBAI | 2 | adam | 2 | MUMBAI | 4 | alex | 2 | MUMBAI | 1 | abhi | 3 | CHENNAI | ...

Words: 1005 - Pages: 5

Sql Injection.

...due to a SQL injection flaw in a web application that communicate with a database. Over ten years have passed since a famous hacker coined the term “SQL injection” and it is still considered one of the major application threats. A lot has been said on this vulnerability, but not all of the aspects and implications have been uncovered, yet. This paper aim is to collate some of the existing knowledge, introduce new techniques and demonstrate how to get complete control over the database management system's underlying operating system, file system and internal network through SQL injection vulnerability in over-looked and theoretically not exploitable scenarios. This paper also discuss about the prevention from the SQL Injection, not only in ORACLE but also in PHP, C#, JAVA and other languages. INDEX ABSTRACT………………………………………………………………………………….....02 INTRODUCTION……………….…………………………….…….………………………….04 BLIND SQL INJECTION…………………………………….………………………………..05 SQL INJECTION OVERVIEW…………………………….………………………………....06 CATEGORIES OF SQL INJECTION ATTACKS…………………………………………..07 WHAT’S VULNERABLE…………………………………………………………..…………08 WHAT’S NOT VULNERABLE…………………………………………………….………….08 SQL INJECTION METHODS……………………………………….……………….……….09 SQL MANIPULATION………………………………………………………..……………….09 CODE INJECTION……………………………………………………….……………………10 FUNCTION CALL INJECTION……………………………………………………………….11 BUFFER OVERFLOWS………………………………………………………………………13 SQL INJECTION TESTING METHODOLOGY………………………………………….....14 PREVENTING SQL......

Words: 3449 - Pages: 14

Sql Plus

...Introduction to Oracle9i: SQL Instructor Guide • Volume 1 40049GC11 Production 1.1 October 2001 D33993 Authors Copyright © Oracle Corporation, 2000, 2001. All rights reserved. Nancy Greenberg Priya Nathan This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Technical Contributors and Reviewers Josephine Turner Martin Alvarez Anna Atkinson Don Bates Marco Berbeek Andrew Brannigan Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. Laszlo Czinkoczki Michael Gerlach Sharon Gray Rosita Hanoman Mozhe Jalali Sarah Jones Charbel......

Words: 33197 - Pages: 133

Sql Defrag

...WHITEPAPER By Juan Rogers With an Introduction to SQL defrag manager SQL SERVER FRAGMENTATION EXPLAINED TM SUMMARY approach to identifying and resolving index fragmentation in SQL Server Warning: This white paper will get a bit technical as it is intended for DBAs who want to truly understand the details and key components of fragmentation in SQL Server. » How to judge the improvements gained by defragmenting your server » » The pros and cons of various approaches to managing fragmentation » The mechanics behind performance robbing data voids » How fragmentation affects performance » The difference between disk and SQL Server internal and external fragmentation The following is a summary of the key topics covered in this paper: monitoring and managing index fragmentation. This technical white paper will help you understand SQL Server fragmentation OVERVIEW As the data in Microsoft SQL Server tables changes their indexes change. Over time these indexes become fragmented. This fragmentation will adversely affect performance. This technical white paper provides information to help you understand the detailed mechanics behind fragmentation. It will also help you understand the methods and approaches for performing defragmentation so you can improve your SQL Server’s......

Words: 3053 - Pages: 13