Database

Need Help in Database Assignment?
We can help you if you are having difficulty with your Database Assignment. Just email your Database Assignment at admin@assignmentcache.com.
We provide help for students all over the world in Database Assignment.

Items 1 to 10 of 35 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4

Grid  List 

Set Ascending Direction
  1. Chapter 3 Case Problem Level 1 Antiques

    Succeeding in Business with Access Chapter 3 Case Problem 1 and 3

    Regular Price: $25.00

    Special Price: $20.00

    Succeeding in Business with Access 2010
    Chapter 3 Analyzing Data for Effective Decision Making

    Case Problem Level 1 - managing Customer Information for NHD Development Group Inc.
    Antiques.accdb complete solution from Step 1 - Step 12.

    Case Problem Level 2 - Retrieving Employee Information for MovinOn Inc.
    MovinOn.accdb complete solution from Step 1 - Step 12.

    Learn More
  2. MSCD610 Exam SQL

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel


    True/False (2 points each)
    Indicate whether the sentence or statement is true or false.
    1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
    2. A table name can consist of numbers, letters, and blank spaces.
    3. A constraint can only be created as part of the CREATE TABLE command.
    4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
    5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
    6. By default, the lowest value that can be generated by a sequence is 0.
    7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
    8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
    9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
    10. By default, the column headings displayed in a report are in upper-case characters.


    Multiple Choice (3 points each)
    Identify the letter of the choice that best completes the statement or answers the question.
    11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
    a. one-to-many c. one-to-all
    b. many-to-many d. one-to-one


    Contents of the BOOKS table
    12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
    a. SELECT title, cost+.20 "New Retail Price" FROM books;
    b. SELECT title, cost*.20 "New Retail Price" FROM books;
    c. SELECT title, cost*1.20 "New Retail Price" FROM books;
    d. none of the above


    Structure of the CUSTOMERS table
    13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
    a. ALTER TABLE customers
    MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
    b. ALTER TABLE customers
    MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
    c. ALTER TABLE customers
    MODIFY (city (+8), lastname (+4));
    d. ALTER TABLE customers
    MODIFY (city (20), lastname (14));


    14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
    a. The constraint exists between two tables, called the parent table and the child table.
    b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
    c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
    d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.


    15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
    a. ALTER USER rthomas PASSWORD EXPIRE ;
    b. ALTER USER rthomas CHANGE PASSWORD;
    c. ALTER USER rthomas UPDATE PASSWORD;
    d. ALTER USER rthomas EXPIRE PASSWORD;


    16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
    a. Asc c. ascending
    b. A d. either a or c


    17. Which of the following is an accurate statement?
    a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
    b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user's session.
    c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
    d. none of the above


    18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
    a. NVL c. IFNVL
    b. IFNL d. NVL2


    Contents of the ORDERS table
    19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
    a. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    b. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL
    GROUP BY order#;
    c. SELECT COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    d. SELECT COUNT(*)
    FROM orders
    WHERE shipdate IS NULL;


    20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
    a. Z
    b. 9
    c. ,
    d. .



    Completion (4 points each)
    Complete each sentence or statement.
    21. A(n) ____________________ is a group of interrelated files.
    22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
    23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
    24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
    25. The ____________________ function is used to round numeric fields to a stated position.



    SQL
    26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
    EMPLOYEE (employee name, street, city)
    WORKS (employee name, company_name, salary)
    A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation


    27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
    EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
    DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
    DEPTLOCATION (DNumber, DLocation)
    PROJECT (PName, PNumber, PLocation, DNum)
    WORKSON (ESSN, PNO, Hours)
    DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)


    Integrity Constraints:
    Primary key = Foreign Key
    EMPLOYEE.SSN = DEPENDENT.ESSN
    EMPLOYEE.SSN = WORKSON.ESSN
    EMPLOYEE.SSN = DEPARTMENT.MGRSSN
    EMPLOYEE.SSN = EMPLOYEE.SuperSSN
    DEPARTMENT.DNumber = EMPLOYEE.DNO
    DEPARTMENT.DNumber = DEPTLOCATION.DNumber
    DEPARTMENT.DNumber = PROJECT.DNum
    PROJECT.PNumber = WORKSON.PNO


    28. (18 points) Write SQL syntax to resolve the following queries.
    - Find the names of all employees who are directly supervised by the employee named “John Doe”
    - List the name of employees whose salary is greater than the average salary of his or her corresponding department
    - For each department, retrieve the department name and the average salary of all employees working in that department.

    Learn More
  3. Access 2010 Chapter 5 Babbage CPA Firm database

    Microsoft Access 2010 Chapter 5 Apply Your Knowledge

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 5 Apply Your Knowledge
    Reinforce the skills and apply the concepts you learned in this chapter.


    Adding Date/Time and OLE Fields, Using an Input Mask Wizard, and Querying Date/Time Fields


    Instructions: Start Access. If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Babbage CPA Firm database that you used in Chapter 4. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Add the Start Date and Picture fields to the Bookkeeper table structure, as shown in Figure 5 – 84. Create an input mask for the Start Date field. Use the Short Date input mask type.
    2. Add the data shown in Table 5–1 to the Bookkeeper table.
    3. Query the Bookkeeper table to find all bookkeepers who started after January 1, 2012. Include the Bookkeeper Number, First Name, Last Name, Hourly Rate, and Earnings YTD in the query results. Save the query as Start Date Query.
    4. Submit the revised database in the format specified by your instructor.

    Learn More
  4. Chapter 7 Lab 1  ECO Clothesline Database

    Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 7 Lab 1 Querying the ECO Clothesline Database Using SQL

    Problem: The management of ECO Clothesline wants to learn more about SQL and has determined a number of questions it wants SQL to answer. You must obtain answers to the questions posed by management.

    Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.

    Perform the following tasks:
    1. Find all customers where the customer type is SAL. Include the Customer Number, Customer Name, and Sales Rep Number fields in the result. Save the query as Lab 7-1 Step 1 Query.
    2. Find all customers located in Tennessee (TN) with a paid amount greater than $1,500.00. Include the Customer Number, Customer Name, and Amount Paid fields in the result. Save the query as Lab 7-1 Step 2 Query.
    3. Find all customers whose names begin with the letter, C. Include the Customer Number, Customer Name, and City fields in the result. Save the query as Lab 7-1 Step 3 Query.
    4. List all cities in descending order. Each city should appear only once. Save the query as Lab 7-1 Step 4 Query.
    5. Display the customer number, name, sales rep number, first name, and last name for all customers. Sort the results in ascending order by sales rep number and customer number. Save the query as Lab 7-1 Step 5 Query.
    6. List the average balance amount grouped by sales rep number. Name the average balance as Average Billed. Save the query as Lab 7-1 Step 6 Query.
    7. Find the customer number and name for every pair of customers who are located in the same city. Save the query as Lab 7-1 Step 7 Query.
    8. Find the customer numbers, names, and sales rep numbers for all customers that have open orders. Use the alias O for the Open Orders table and C for the Customer table. Each customer should appear only once. Save the query as Lab 7-1 Step 8 Query.
    9. Use a subquery to find all sales reps whose customers are located in Pineville. Save the query as Lab 7-1 Step 9 Query.
    10. Find the average balance amount for sales rep 44. Save the query as Lab 7-1 Step 10 Query.
    11. Submit the revised database in the format specified by your instructor.

    Learn More
  5. Microsoft Access 2010 Chapter 8 Lab 1 Customer View and Update Form

    Microsoft Access 2010 Chapter 8 Lab 1 Applying Advanced Form Techniques to the ECO Clothesline Database

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 8 Lab 1 Applying Advanced Form Techniques to the ECO Clothesline Database


    Lab 1: Applying Advanced Form Techniques to the ECO Clothesline Database
    Problem: ECO Clothesline needs a form for the Customer table that will allow users to update data in the table ECO also needs a form to display open orders data for sales reps.


    Instructions: If you are using Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 7. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Create a query that includes the Sale Rep Number field from the Sales Rep tan;e, the Customer Number field from the Customer table and the Order Number and Amount fields from the Open Orders table. Sort the query in ascending order by Sales Rep Number, Customer Number, and Order NUmber Save the query as Sales Rep and Orders.


    2. Create the form shown in Figure 8-95. total Amount is a calculated control and is the sum of the Balance and Amount Paid amounts. Format the Total Amount control as Currency with two decimal places. (Hint: Use the property sheet for the control.) the form includes command buttons, a combo box for the Sales Rep Number field, and a combo box to search for customers by name. Be sure to sort the customer by names in ascending order, place a rectangle around the combo box, and update the combo box. The user should not be able to tab to the combo box. When the Add Record button is clicked, the insertion point should be in the customerNumber field. The form is similar in style to the form shown in Figure 8-1 on page AC 466.


    3. Create the Sales Rep Order Data form shown in Figure 8-96. The subform that appears in the Datasheet tab uses the Sales Reps and Orders query (Figure 8-96a). The chart ghat appears in the Chart tab uses the same query (Figure 8-96b).


    4. Submit the revised database in the format specified by your instructor.

    Learn More
  6. Microsoft Access 2010 Chapter 11 Lab 1 Designing a Database for Phils Limousine

    Microsoft Access 2010 Chapter 11 Lab 1 Designing a Database for Phils Limousine

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 11 Lab 1 Designing a Database for Phils Limousine


    Lab 1: Designing a Database for Phil’s Limousine
    Instructions: Consider the following set of requirements for Phil’s Limousine Service:
    • For each driver, the company keeps track of the driver’s name (first name and last name), driver’s address (street, city, state, postal code), driver’s license number, home telephone number, and cell telephone number.
    • For each limousine, the company keeps track of a unique limousine ID number, limousine license number, limousine color, and number of passengers the limousine can hold.
    • A driver can be assigned to more than one limousine. A limousine will have multiple drivers but only one driver at a time.
    Based on these requirements, do the following:
    1. Identify and list the entities and the attributes of those entities.
    2. Identify and list the functional dependencies.
    3. Create a set of 3NF relations using the shorthand notation given in the chapter. Be sure to identify all primary keys and foreign keys appropriately.
    Submit your database design in the format specified by your instructor.

    Learn More
  7. Assignment 9-4 Updating Stock Levels When an Order ls Cancelled

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

    Regular Price: $25.00

    Special Price: $20.00

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

    Assignment 9-1: creating a Tigger to Handle Product Restocking
    Brewbean's has a couple of columns in the Product table to assist in inventory tracking. The REORDER column contains the stock level at which the product should be reordered. If the stock fall to this level. Brewbean's wants the application to insert a row in the BB_PRODUCT_REQUEST table automatically to alert the ordering clerk that additional inventory is needed. Brewbean's currently uses the reorder level amount as the quantity that should be ordered. This task can be handled by using a trigger.
    1. Take out some scrap paper and a pencil. Think about the tasks the triggers needs to perform. Including checking. whether the new stock level falls below the reorder point. If so, check whether the product is already on order by viewing the product request table; if not, enter a new product request. Try to write the trigger code on paper. Even though you learn a lot by reviewing code, you improve your skills faster when you create the code on your own.
    2. Open the c9reorder.txt file in the Chapter09 folder. Review this trigger code, and determine how it compares with your code.
    3. In SQL Developer, create the trigger with the provided code.
    4. Test the trigger with product ID 4. First, run the query shown in Figure 9-36 to verify the current stock data for this product. Notice that a sale of one more item should initiate a reorder.
    5. Run the UPDATE statement shown in Figure 9-37. It should cause the trigger to fire. Notice the query to check whether the trigger fired and weather a product stock request was inserted in the BB_PRODUCT_REQUEST table.
    6. Issue a ROLLBACK statement to undo these DML actions to restore data to its original state for use in later assignments.
    7. Run the following statement to disable this trigger so that it doesn't affect other projects:
    ALTER TRIGGER bb_reorder_trg DISABLE;


    Assignment 9-2: Updating Stock Information When a Product Is Filled
    Brewbean's has a BB_PRODUCT_REQUEST table where requests to refill stock level was inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table's DTRECD and COST columns. they want the stock level in the product table to be updated. Create a trigger named BB_REQFILL_TRG to handle this task, using the following steps as a guideline:
    1. In SOL Developer, run the following INSERT statement to create a product request you can use in this assignment:
    INSERT INTO bb_product_requect (idRequest, idProduct, dtRequest, qty)
    VALUES (3, 5, SYSDATE, 45);
    COMMIT;
    2. Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory.
    3. Now test the trigger. First, query the stock and reorder data for product 5. as shown in Figure 9-38.
    4. Now update the product request to record it as fulfilled by using UPDATE statement shown in figure 9-39.
    5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue the ROLLBACK statement to undo modifications.
    6. If you aren't doing assignment 9-3, disable the trigger so that it doesn't affect other assignments.


    Assignment 9-3: Updating the Stock Level If a Product Fulfillment is Cancelled
    The Brewbean's developers have made progress on the inventory-handling processes; however, they hit a snag when a store clerk incorrectly recorded a product request as fulfilled. When the product request was updated to record a DTRECD value, the product stock level was updated automatically via an existing trigger, BB_REQFILL_TRG. If the clerk empties the DTRECD column to indicate that the product request has been filled, the product stock level need to be corrected or reduced, too. Modify the BB_REQFILL_TRG to solve this problem.
    1. Modify the trigger code from Assignment 9-2 as needed. Add code to check whether the DTRECD column already has a data in it and is now being set to NULL.
    2. Issue the following DML actions to create or update rows that you can use to test the trigger:
    INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty, dtRecd, cost)
    VALUES (4, 5, SYSDATE, 45, '15-JUN-2012', 225);
    UPDATE bb_product
    SET stock = 86
    WHERE idProduct = 5;
    COMMIT;
    3. Run the following UPDATE statement to test the trigger, and issue queries to verify that the data has been modified correctly.
    UPDATE bb_product_request
    SET dtRecd = NULL
    WHERE idRequest = 4;
    4. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
    ALTER TRIGGER bb_reqfill_trg DISABLE;


    Assignment 9-4: Updating Stock Levels When an Order ls Cancelled
    At times, customers make mistakes in submitting orders and call to cancel an order. Brewbean's wants to create a trigger that automatically updates the stock level of all products associated with a cancelled order and updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order wasn't completed. Create a trigger named BB_ORDCANCEL_TRG to perform this task, taking into account the following points:
    The trigger need to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates an order has been cancelled.
    Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop might be a suitable mechanism for updating each item's stock level
    Keep in mind that coffee can be ordered in half or whole pounds.
    Use basket 6, which contains two items, for testing.
    1.Run this INSERT statement to test the trigger:
    INSERT INTO bb_basketstatus (idStatus, idBasket, idStage, dtStage)
    VALUES (bb_status_seq.NEXTVAL, 6, 4, SYSDATE);
    2. Issue the queries to confirm that the trigger has modified the basket's order status and product stock level correctly.
    3. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
    ALTER TRIGGER BB_ORDCANCEL_TRG DISABLE;

    Learn More
  8. Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL

    Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL

    Regular Price: $30.00

    Special Price: $25.00

    Microsoft Access 2010 Chapter 7 Lab 2 Querying the Walburg Energy Alternatives Database Using SQL


    Problem: The manager of the Walburg Energy Alternatives store would like to learn more about SQL and has determined a number of questions he wants SQL to answer. You must obtain answers to the questions posed by the manager.
    Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 6. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Find all records in the Item table where the difference between the cost of the item and the selling price of the item is less than $0.25 (25 cents). Display the item number, description, cost, and selling price in the query result. Save the query as Lab 7-2 Step 1 Query.
    2. Display the item number, description, and profit (selling price – cost) for all items. Name the computed field Profit. Save the query as Lab 7-2 Step 2 Query.
    3. Find all items where the description begins with the letter, W. Include the item number and description in the result. Save the query as Lab 7-2 Step 3 Query.
    4. Display the vendor name, item number, description, and cost for all items where the number on hand is less than 10. Sort the results in ascending order by vendor name and description. Save the query as Lab 7-2 Step 4 Query.
    5. Find the average cost by vendor. Name the computed field Average Cost. Save the query as Lab 7-2 Step 5 Query.
    6. Find the total number of reordered items for each item in the Reorder table. Name the computed field Total Ordered. Include the item number in the result. Save the query as Lab 7-2 Step 6 Query.
    7. Add the following record to the Reorder table.
    Item Number Date Ordered Number Ordered
    8590 4/12/2012 3
    Save the steps to add the record as Lab 7-2 Step 7 Query.
    8. Update the Number Ordered field to 5 for those records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to update the record as Lab 7-2 Step 8 Query.
    9. Delete all records where the Item Number is 8590 and the date ordered is 4/12/2012. Save the steps to delete the record as Lab 7-2 Step 9 Query.
    10. Submit the revised database in the format specified by your instructor.

    Learn More
  9. Microsoft Access 2010 CHAPTER 4 Lab 2 Filtered Inventory Status Report

    Microsoft Access 2010 Chapter 4 Lab 2 Presenting Data in the Walburg Energy Alternatives Database

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 4 Lab 2 Presenting Data in the Walburg Energy Alternatives Database


    Problem: The management of Walburg Energy Alternatives already has realized the benefits from the database of items and vendors that you created. The management now would like to prepare reports and forms for the database.
    Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Walburg Energy Alternatives database that you used in Chapter 3. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Open in Layout view the Inventory Status Report that you created in Chapter 1. Add a total for the Inventory Value field. Be sure the total is completely displayed. Display the average cost. If there are fewer than 10 items on hand, the value should appear in a red bold font. Filter the report for all items
    where the number on hand is 5 or less. Save the filtered report as Filtered Inventory Status Report.


    2. Create the Items by Vendor report shown in Figure 4 – 84.


    3. Create the form shown in Figure 4 – 85. If there are fewer than 10 items on hand, the value should appear in a red bold font. Save the form as Item Update Form.


    4. Filter the Item Update Form for all items where the cost is less than $3.00 and sort the results in descending order by cost. Save the form as Filtered Item Update Form.


    5. Submit the revised database in the format specified by your instructor.

    Learn More
  10.  ITS407 Module 8 Entities and Attributes

    ITS407 Module 8 Project Ace Software MySQL Database For Mom and Pop Johnson video store

    Regular Price: $25.00

    Special Price: $20.00

    ITS407 Module 8 Project Ace Software MySQL Database For Mom and Pop Johnson video store


    You are a database consultant with Ace Software, Inc., and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.


    You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent, therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.


    The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors and at different wholesale prices.


    Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop, and the information from these catalogs must be included in the database.


    Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the Academy Awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.


    There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and Academy Awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.


    1) Identify and describe the entities and their attributes.
    2) Develop relationship sentence pairs.
    3) Draw an ERD with Visio.
    4) Develop metadata from the ERD and document in an Excel spreadsheet.
    5) Using your selected RDBMS (SQL Server, Oracle, or MySQL), develop and execute an SQL script file of DDL SQL to create the database tables in the metadata document.
    6) Using your selected RDBMS, develop and execute an SQL script file of DML SQL INSERT statements to populate the tables using SQL INSERT statements for at least 5 rows of data per table.
    7) Using your selected RDBMS develop and execute an SQL script file to:
     a) Show the contents of all tables
     b) Retrieve all of the customers' names, account numbers, and addresses (street and zip code only), sorted by account number
     c) Retrieve all of the DVDs rented in the last 30 days and sort in chronological rental date order
     d) Update a customer name to change their maiden names to married names. You can choose which row to update. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.
     e) Delete a specific customer from the database. You can choose which row to delete. Make sure that you use the primary key column in your WHERE clause to affect only a specific row.


    The metadata should be submitted in an Excel spreadsheet. All other outputs for the database design, SQL code, and SQL results should be submitted in a single Word file in order, by step, and clearly labeled.

    Learn More

Items 1 to 10 of 35 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4

Grid  List 

Set Ascending Direction