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 201 total

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

Grid  List 

Set Descending Direction
  1. Oracle Database to Track Students Grade ERD

    Oracle Database to Track Students Grade

    Regular Price: $15.00

    Special Price: $12.00

    Oracle Database to Track Students Grade


    Build an Oracle database to track students grade in a class:
    The database tracks
    *Student Information
    *Instructor Information
    *Class information
    *Grading Breakdown
    *Students' Grades


    Different queries will show
    *List of students with semester grades
    *List of students who received an A
    *Average grades
    *Above average grades

    Learn More
  2. Joan Casteel Oracle 11g SQL Chapters 10 Multiple Choice Questions

    Joan Casteel Oracle 11g SQL Chapters 10 Multiple Choice Questions Solution

    Regular Price: $12.00

    Special Price: $10.00

    Joan Casteel Oracle 11g SQL Chapters 10 Multiple Choice Questions Solution


    To answer the following questions, refer to the tables in the JustLee Books database.
    1. Which of the following is a valid SQL statement?
    a. SELECT SYSDATE;
    b. SELECT UPPER(Hello) FROM dual;
    c. SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual;
    d. all of the above
    e. none of the above


    2. Which of the following functions can be used to extract a portion of a character string?
    a. EXTRACT
    b. TRUNC
    c. SUBSTR
    d. INITCAP


    3. Which of the following determines how long ago orders that haven’t shipped were received?
    a. SELECT order#, shipdate-orderdate delay FROM orders;
    b. SELECT order#, SYSDATE – orderdate FROM orders WHERE shipdate IS NULL;
    c. SELECT order#, NVL(shipdate, 0) FROM orders WHERE orderdate is NULL;
    d. SELECT order#, NULL(shipdate) FROM orders;


    4. Which of the following SQL statements produces “Hello World” as the output?
    a. SELECT "Hello World" FROM dual;
    b. SELECT INITCAP('HELLO WORLD') FROM dual;
    c. SELECT LOWER('HELLO WORLD') FROM dual;
    d. both a and b
    e. none of the above


    5. Which of the following functions can be used to substitute a value for a NULL value?
    a. NVL
    b. TRUNC
    c. NVL2
    d. SUBSTR
    e. both a and d
    f. both a and c


    6. Which of the following is not a valid format argument for displaying the current time?
    a. 'HH:MM:SS'
    b. 'HH24:SS'
    c. 'HH12:MI:SS'
    d. All of the above are valid.


    7. Which of the following lists only the last four digits of the contact person’s phone number at American Publishing?
    a. SELECT EXTRACT(phone, -4, 1) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING';
    b. SELECT SUBSTR(phone, -4, 1) FROM publisher WHERE name = 'AMERICAN PUBLISHING';
    c. SELECT EXTRACT(phone, -1, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';
    d. SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';


    8. Which of the following functions can be used to determine how many months a book has been available?
    a. MONTH
    b. MON
    c. MONTH_BETWEEN
    d. none of the above


    9. Which of the following displays the order date for order 1000 as 03/31?
    a. SELECT TO_CHAR(orderdate, 'MM/DD') FROM orders WHERE order# = 1000;
    b. SELECT TO_CHAR(orderdate, 'Mth/DD') FROM orders WHERE order# = 1000;
    c. SELECT TO_CHAR(orderdate, 'MONTH/YY') FROM orders WHERE order# = 1000;
    d. both a and b
    e. none of the above


    10. Which of the following functions can produce different results, depending on the value of a specified column?
    a. NVL
    b. DECODE
    c. UPPER
    d. SUBSTR


    11. Which of the following SQL statements is not valid?
    a. SELECT TO_CHAR(orderdate, '99/9999') FROM orders;
    b. SELECT INITCAP(firstname), UPPER(lastname) FROM customers;
    c. SELECT cost, retail, TO_CHAR(retail-cost, '$999.99') profit FROM books;
    d. all of the above


    12. Which function can be used to add spaces to a column until it’s a specific width?
    a. TRIML
    b. PADL
    c. LWIDTH
    d. none of the above


    13. Which of the following SELECT statements returns 30 as the result?
    a. SELECT ROUND(24.37, 2) FROM dual;
    b. SELECT TRUNC(29.99, 2) FROM dual;
    c. SELECT ROUND(29.01, -1) FROM dual;
    d. SELECT TRUNC(29.99, -1) FROM dual;


    14. Which of the following is a valid SQL statement?
    a. SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;
    b. SELECT ROUND(TRUNC(125.38, 0) FROM dual;
    c. SELECT LTRIM(LPAD(state, 5, ' '), 4, -3, "*") FROM dual;
    d. SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM dual;


    15. Which of the following functions can’t be used to convert the letter case of a character string?
    a. UPPER
    b. LOWER
    c. INITIALCAP
    d. All of the above can be used for case conversion.


    16. Which of the following format elements causes months to be displayed as a three-letter abbreviation?
    a. MMM
    b. MONTH
    c. MON
    d. none of the above


    17. Which of the following SQL statements displays a customer’s name in all uppercase
    characters?
    a. SELECT UPPER('firstname', 'lastname') FROM customers;
    b. SELECT UPPER(firstname, lastname) FROM customers;
    c. SELECT UPPER(lastname, ',' firstname) FROM customers;
    d. none of the above


    18. Which of the following functions can be used to display the character string FLORIDA in the query results whenever FL is entered in the State field?
    a. SUBSTR
    b. NVL2
    c. REPLACE
    d. TRUNC
    e. none of the above


    19. What’s the name of the table provided by Oracle 11g for completing queries that don’t involve a table?
    a. DUMDUM
    b. DUAL
    c. ORAC
    d. SYS


    20. If an integer is multiplied by a NULL value, the result is:
    a. an integer
    b. a whole number
    c. a NULL value
    d. None of the above—a syntax error message is returned.

    Learn More
  3. MSCD610 Week 7 Logical ERD

    MSCD610 Week 7 Course Project Details Hotel Database

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Week 7 Course Project Details Hotel Database


    The course project is to develop a Data Model and Database Design for a set of business case requirements. Students should informally review their ER model with the facilitator in week 4 or 5.


    This project should follow a top-down database design process and produce the following outputs:
    1. A set of business information requirements in the form of a business case abstract.


    2. A complete ERD that models those requirements.
    This model should include the following:
    a. A definition of each entity
    b. The unique identifier for each entity
    c. The attributes associated with each entity
    d. The relationships between the entities including their cardinality, optionally, and names


    3. A physical implementation of a database from the logical design (ERD).
    The tables should be normalized to 3NF. For this design include:
    a. The name for each table
    b. The primary key for the table, and any secondary keys
    c. Any foreign keys
    d. A data dictionary
    e. Any sample data available


    4. The DDL scripts for implementing the physical database design for this database. Include the referential integrity constraints for these tables. Also include the DDL for any needed indexes and/or views. If necessary, indicate any database tuning which is anticipated on this database.


    5. Create the designed Oracle database, and load it with sample data. Then print the definition of the tables, indexes, and any views. Print sample report(s) showing the query definitions in the business requirements have been met.

    Learn More
  4. 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
  5. Microsoft Access 2010 CHAPTER 3 Lab 2 Reorder Filter

    Microsoft Access 2010 Chapter 3 Lab 2 Maintaining the Walburg Energy Alternatives Database

    Regular Price: $20.00

    Special Price: $15.00

    Microsoft Access 2010 Chapter 3 Lab 2 Maintaining the Walburg Energy Alternatives Database


    Problem: The management of the Walburg Energy Alternatives recently acquired some items from a store that is going out of business. You now need to append these new items to the current item table. You also need to change the database structure and add some validation rules to the database.
    Use the database modified in the In the Lab 2 of Chapter 2 on page AC 134 for this assignment. You also will use the More Items database from the Data Files for Students. See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the required files.


    Perform the following tasks:
    1. Open the More Items database from the Data Files for Students.
    2. Create a new query for the Item table and add all fields to the query.
    3. Using an append query, append all records in the More Items database to the Item table in the Walburg Energy Alternatives database, as shown in Figure 3 – 87.
    4. Save the append query as Walburg Append Query and close the More Items database.
    5. Open the Walburg Energy Alternatives database and then open the Item table in Datasheet view. There should be 20 records in the table.
    6. The items added from the More Items database do not have a vendor assigned to them. Assign items 1234 and 2234 to vendor JM. Assign item 2216 to vendor AS. Assign items 2310 and 2789 to vendor SD.
    7. Create an advanced filter for the Item table. The filter should display records with fewer than 10 items on hand and be sorted in ascending order by Description. Save the filter settings as a query and name the filter Reorder Filter.
    8. Make the following changes to the Item table:
    a. Change the field size for the On Hand field to Integer. The Format should be fixed and the decimal places should be 0.
    b. Make Description a required field.
    c. Specify that the number on hand must be between 0 and 50. Include validation text.
    d. Add a calculated field Inventory Value (On Hand*Cost) following the Cost field. Format the field as currency.
    9. Save the changes to the table design. If a dialog box appears indicating that some data may be lost, click the Yes button.
    10. Add the Inventory Value field to the Inventory Status Report. Place the field after the Cost field. Save the changes to the report.
    11. Specify referential integrity between the Vendor table (the one table) and the Item table (the many table). Cascade the update but not the delete.
    12. Submit the revised More Items database and the Walburg Energy Alternatives database in the format specified by your instructor.

    Learn More
  6. Student Oracle Database Part1 Create Table

    Student Database Oracle DDL and Queries

    Regular Price: $40.00

    Special Price: $30.00

    Student Database Oracle DDL and Queries


    Create the following tables.
    STUDENT
    Student Number (PK)
    Student Last Name
    Student Major
    Department ID (FK)
    Student GPA
    Student Hours
    Student Class
    Advisor ID (FK)


    ADVISOR
    Advisor ID (PK)
    Advisor Last Name
    Advisor Office
    Advisor Building
    Advisor Phone


    DEPARTMENT
    Department ID (PK)
    Department Code
    Department Name
    Department Phone


    *NOTE* You will have to decide on how to handle the Department and Advisor ID foreign keys in terms of a numbering system as well as appropriate field widths and types for the fields.
    The business rules which govern this database are: A student may have one advisor while an advisor may advise multiple students. A student belongs to only one department but each department can have many students.
    Populate the table with data from Table P6.4 (p. 217).

    There is an error in the book! Change Ortiz’s student number to be 200888. In addition, add these three students to your database.
    STU_NUM 123984 995133 367181
    STU_LNAME Freeman Wilder Green
    STU_MAJOR CIT CIT BIS
    DEPT_CODE CS CS IS
    DEPT_NAME Computer Science Computer Science Business Informatics
    DEPT_PHONE 5234 3951 3951
    COLLEGE_NAME Informatics Informatics Informatics
    ADVISOR_LNAME Strand Zhang Goh
    ADVISOR_BLDG Griffin Griffin Griffin
    ADVISOR_OFFICE 5132 3451 5612
    ADVISOR_PHONE 1603 3512 7922
    STU_GPA 2.5 3.9 2.3
    STU_HOURS 97 58 63
    STU_CLASS Senior Junior Junior


    Part 1
    Provide all DDL-related code. This includes table definition and creation, fk/pk creation, and populating the tables with data. Please include the output from Oracle that shows that everything was created correctly.


    Part 2
    Queries – For each query provide 1) What the output, specifically, should be based upon eyeballing the data 2) the SQL code used to generate the query, and 3) the output from Oracle. Please include any relevant fields you think the user of the query would need to interpret the output.
    1. Advisors need the capability to generate a query that returns *all* student information based upon a student number. This will help them in the advisement process. Create a query that returns all information for student Freeman from each table.
    2. Each year, college administrators need to know how many students are in each major. Create a query that counts the number of students in each major while displaying the major name.
    3. Kroger has approached NKU with an internship opportunity! The business department chair needs to generate a mailing list to inform great students about a job opportunity. Create a query that shows all the student numbers, last names, majors, department names, and advisor’s last name for students in Business Admin. Students should have a minimum GPA of 2.5 or greater to be on this mailing list. List in ascending order of GPA.
    4. To get an idea of the adequacy of admission standards, NKU needs to have an idea of the breakdown of students. By each college, show the number of students in each classification (freshmen, sophomore, junior, senior).
    5. Due to a fire, Griffin Hall has burnt down. Write a SQL statement that updates all faculty who had offices in Griffin Hall to now be housed in the University Center.
    6. New funding may be able to pay for an advising center. The dean would like to get an idea of how many students each faculty member currently advises. Create a query that shows the names and majors of students for each advisor along with the advisor’s name.
    7. (Extra credit) Create a query that counts the number of students who are eligible for the Dean’s list (GPA >= 3.5) in each department.

    Learn More
  7. 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
  8. Access Chapter 7 Make It Right Query 1

    Microsoft Access 2010 Chapter 7 Make It Right Correcting Errors in the Query Design

    Regular Price: $20.00

    Special Price: $15.00

    Microsoft Access 2010 Chapter 7 Make It Right Correcting Errors in the Query Design

    Analyze a database, correct all errors, and/or improve the design.

    Instructions: Start Access. Open the College Pet Sitters database. See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the files required in this book.

    College Pet Sitters is a database maintained by a small pet-sitting business owned by college students. The queries shown in Figure 7 – 37 contain a number of errors that need to be corrected before the queries run properly. The query shown in Figure 7 – 37a displays the Enter Parameter Value dialog box, but this is not a parameter query. Also, the owners wanted to assign the name, Total Amount, to the Balance + Paid calculation. Save the query with your changes.

    When you view the results for the query shown in Figure 7 – 37b, you get 30 records. You know this is wrong. Also, the query did not sort correctly. The query results should be sorted first by sitter number and then by descending balance. Correct the errors and save the query with your changes.

    Change the database properties, as specified by your instructor. Submit the revised database in the format specified by your instructor.

    Learn More
  9. Microsoft Access 2010 Chapter 5 Lab 2 Adding Fields and Creating Multitable Forms for the Walburg Energy Alternatives Database

    Microsoft Access 2010 Chapter 5 Lab 2 Adding Fields and Creating Multitable Forms for the Walburg Energy Alternatives Database

    Regular Price: $25.00

    Special Price: $20.00

    Microsoft Access 2010 Chapter 5 Lab 2 Adding Fields and Creating Multitable Forms for the Walburg Energy Alternatives Database

    Problem: The management of Walburg Energy Alternatives has found that they need to maintain additional data on suppliers. Management needs to keep track of the last date an order was placed, whether the vendor accepts returns, and whether the vendor allows online ordering. Management also would like to attach to each vendor’s record Excel files that contain historical cost data. Walburg Energy Alternatives requires a form that displays information about the vendor as well as the items that are purchased from vendors.

    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 4. Otherwise, see the inside back cover of this book for instructions on downloading the Data Files for Students, or contact your instructor for more information about accessing the required files.

    Perform the following tasks:
    1. Add the fields Last Order Date, Returns, Online Ordering, and Cost History to the end of the Vendor table structure. Last Order Date is a Date/Time field, Returns and Online Ordering are Yes/No fields, and Cost History is an Attachment field. Create an input mask for the Last Order Date that uses the Short Date input mask.
    2. Add the data shown in Table 5 – 3 to the Vendor table.
    Table 5 – 3 Data for Vendor Table
    Vendor Code Last Order Date Returns Online Ordering Cost History
    AS 3/30/2012 Yes No AS_History.xlsx
    JM 3/26/2012 No Yes JM_History.xlsx
    SD 4/4/2012 Yes Yes SD_History.xlsx
    3. Create the form shown in Figure 5 – 88. Use Vendor Master Form as the name of the form and Items of Vendor as the name of the subform. The title is raised, semi-bold, and distributed with a font size of 24. The labels are blue, bold, and etched with a transparent border style. The fields have a sunken special effect.
    4. Open the Vendor Master Form and then open the cost history for Asterman Industries. Change the previous cost for item 4553 to $40.95. Save the change to the workbook.
    5. Query the Vendor table to find all vendors that accept returns and allow online ordering. Include the Vendor Code and Name in the query results. Save the query as Returns-Online Query.
    6. 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 201 total

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

Grid  List 

Set Descending Direction