1 / 83

CIS 336 str Effective Communication - tutorialrank.com

For more course tutorials visit<br>www.tutorialrank.com<br><br>CIS 336 Final Exam Guide<br> 1)Joe works for a company where the IT department charges him for the number of CRM login accounts that are in his department. What type of IT funding model is his company deploying?<br> <br>

Bartholomew20
Download Presentation

CIS 336 str Effective Communication - tutorialrank.com

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CIS 336 Final Exam (May,2019) For more course tutorials visit www.tutorialrank.com CIS 336 Final Exam Question 1. 1. (TCO 1) A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions? Question 2. 2. (TCO 1) A relational DBMS provides protection of the _____ through security, control, and recovery facilities. Question 3. 3. (TCO 2) A relationship is an association between _____ Question 4. 4. (TCO 2) A _____ key is a key that is composed of more than one attribute. Question 5. 5. (TCO 3) The PK must uniquely identify each entity instance. A primary key must be able to guarantee unique values. It cannot contain nulls. This rationale belongs to _____ Question 6. 6. (TCO 3) The extended entity relationship model (EERM) is sometimes referred to as the _____ Question 7. 7. (TCO 3) A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in _____

  2. Question 8. 8. (TCO 4) When a constraint is created at the _____ level with the CREATE TABLE command, the constraint definition is simply included as part of the column definition. (Points : 4) Question 1. 1. (TCO 4) When creating a table, which of the following statements is NOT correct? Question 2. 2. (TCO 4) Which of the following SQL statements would you use to create a table named DEVRY? Question 3. 3. (TCO 5) The Crow's Foot model is more _____-oriented than the Chen model. Question 4. 4. (TCO 5) Knowing the _____ number of entity occurrences is very helpful at the application software level. Question 5. 5. (TCO 6) The _____ statement can be used to enable or disable a constraint. Question 6. 6. (TCO 6) When modifying the data characteristics of a column in Oracle, which of the following is correct? Question 7. 7. (TCO 6) Which of the following keywords can be used to change the size, datatype, or default value of an existing column? Question 8. 8. (TCO 7) Any type of subquery can be used in the _____ clause of a SELECT statement Question 1. 1. (TCO 7) Which of the following subqueries returns more than one row of results to the outer query? Question 2. 2. (TCO 7) To list a unique value, where the list will produce only a list of those values that are different from one another, you will write the command _____

  3. Question 3. 3. (TCO 8) Based upon the contents of the BOOK_ORDER table shown below, which of the following SQL statements will display the ship date for order 1006 as April 2, 2002? Question 4. 4. (TCO 8) Which of the following format elements will display insignificant leading zeros? Question 5. 5. (TCO 8) Which of the following functions determines the number of characters in a character string? Question 6. 6. (TCO 9) The column to be updated by the UPDATE command is specified in the _____ clause. Question 7. 7. (TCO 9) Using the promotion table shown below, which of the following SQL statements will insert a new row into the PROMOTION table? Question 8. 8. (TCO 10) Which of the following commands is used to establish a user account? Question 9. 9. (TCO 10) Which command will delete a user account from Oracle9i? Question 1. 1. (TCO 2) Explain what physical independence means. Question 2. 2. (TCO 3) What is a composite primary key and in what situation are they most commonly used? Give an example. Question 3. 3. (TCO 7) Sally needs to find all employees who have a date of hire between 01-JAN-2003 and 01-JAN-2005. She writes and executes the following query. Question 4. 4. (TCO 8) In a table named ORDER, there is a column named SHIPDATE that contains a date for each order that has been

  4. shipped. Write the query that would show the number of orders that have been shipped. List the column name as Number of Orders Shipped for this derived column. Question 5. 5. (TCO 6) Write the SQL statement that will remove the database table EMPLOYEE. Question 1. 1. (TCO 7) Write a join query that for every order placed on September 5, 1998, (use '05-SEP-1998') will list the order number and order date along with the customer number, last name, and first name of the customer who placed the order. Question 2. 2. (TCO 8) Write a query that will list the part number, part description, and on-hand value (units on hand * unit price) for each part in item class AP. Give the expression an alias of on-hand value. Question 3. 3. (TCO 8) Write a subquery that will list the part number, part description, and units on hand of all parts where the number of units on hand is greater than the average of units on hand for all parts. Question 4. 4. (TCO 7) Using the orders table, write a query that will list the order number, date, and customer number for every order that was made prior to September 5, 1998. Format the date in your result set to be in the same format as above (i.e., September 5, 1998). Question 5. 5. (TCO 7) Using a NATURAL JOIN method, write a query that will list the customer number, last name, and first name for every customer who currently has an order on file for an iron. ****************************************************** CIS 336 Final Exam 1

  5. For more course tutorials visit www.tutorialrank.com Tutorial Purchased: 3 Times, Rating: A 1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) Data constitutes the building blocks of _____________. (Points : 4) information processing applications programming 3. (TCO 2) If a foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key is/are said to exhibit __________ integrity. (Points : 4)

  6. referential restrictive secondary redundant 4. (TCO 2) In a "one-to-many" relationship, which constraint is usually added to the "many" table? (Points : 4) UNIQUE PRIMARY KEY FOREIGN KEY NOT NULL 5. (TCO 3) A ____ occurs when a relationship is improperly or incompletely identified and, therefore, is represented in a way that is not consistent with the real world. (Points : 4) surrogate primary keys time-variant data design trap fan trap 6. (TCO 3) All of the following are true about a prime attribute except_____________. (Points : 4) it is a key attribute it is at least part of a key it is not a key attribute it may be part of a composite key

  7. 7. (TCO 3) The conflicts between design efficiency, information requirements, and processing speed are often resolved through_____________. (Points : 4) conversion from 1NF to 2NF conversion from 2NF to 3NF compromises that include denormalization conversion from 3NF to 4NF 8. (TCO 4) When a constraint is created at the ______ level with the CREATE TABLE command, the constraint definition is simply included as part of the column definition.(Points : 4) table column database row Page 2 - Multiple Choice 1. (TCO 4) In Oracle, the CONSTRAINT clause is used to ___________________. (Points : 4) create only referential constraints define and name any constraint create a short cut so the constraint does not have to be named create only entity entegrity constraints 2. (TCO 4) The SQL command that lets you list the table contents is (Points : 4) insert. select.

  8. commit. update. rollback. 3. (TCO 5) The Crow's Foot model is more ___________-oriented than the Chen model. (Points : 4) object user implementation processor 4. (TCO 5) The ___________ model is both software and hardware dependent. (Points : 4) conceptual logical condensed physical 5. (TCO 6) Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store the date that the customer first placed an order with the company? (Points : 4) CREATE COLUMN firstorderdate, DATE TO customers; ALTER TABLE customers ADD COLUMN firstorderdate DATE; ALTER TABLE customers ADD firstorderdate DATE; ALTER TABLE customers ADD (firstorderdate DATE); 6. (TCO 6) When modifying the data characteristics of a column in Oracle, which of the following is correct? (Points : 4)

  9. The column size can be increased. The size of the column can be decreased. Changing the default value of a column will change the values of data already in a table. If a NUMBER column is empty, its precision and scale cannot be changed. 7. (TCO 6) Which of the followig SQL commands would alter the table DEVRY and add a foreign key that references the CITY table? (Points : 4) ALTER table DEVRY ADD CONSTRAINT devry_studentcity_fk references city; ALTER table DEVRY ADD CONSTRAINT devry_studentcity_fk FOREIGN KEY references city; ALTER table DEVRY ADD CONSTRAINT devry_studentcity_fk FOREIGN KEY (student_city) references city; ALTER table DEVRY ADD CONSTRAINT FOREIGN KEY (student_city) references city; 8. (TCO 7) Given a table named EMPLOYEE, the SQL command to sort records in a specific order would be: (Points : 4) SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE LIST BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE

  10. ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE DISPLAY BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE SEQUENCE BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; Page 3 - Multiple Choice 1. (TCO 7) Which of the following subqueries returns more than one row of results to the outer query? (Points : 4) multiple-column subquery single-row subquery multiple-row subquery correlated subquery 2. (TCO 7) Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column? (Points : 4) NATURAL JOIN JOIN...ON JOIN...USING JOIN...WHERE 3. (TCO 8) Based upon the contents of the BOOKS table shown below, which of the following SQL statements will return the number of categories contained in the table?

  11. (Points : 4) SELECT COUNT(DISTINCT category) FROM books; SELECT DISTINCT COUNT(category) FROM books; SELECT DISTINCT (COUNT(category)) FROM books; SELECT DISTINCT COUNT(category) FROM books; 4. (TCO 8) The _____________ function can be used to display upper- case characters in lower-case. (Points : 4) CONVERT MIN LESS LOWER 5. (TCO 8) Which of the following statements will produce the value 21.1? (Points : 4) SELECT ROUND(21.12, 1) FROM dual; SELECT ROUND(21.12, -1) FROM dual; SELECT ROUND(21.12, 0) FROM dual; SELECT ROUND(21.12, 0+) FROM dual; 6. (TCO 9) The column to be updated by the UPDATE command is specified in the ______ clause. (Points : 4) UPDATE SET WHERE COL

  12. 7. (TCO 9) If the INSERT INTO command is used to insert data values that violate an existing constraint in a table, which of the following will happen. (Points : 4) Oracle will raise an error and you will have to go update the incorrect data. Oracle will raise an error and the entire row will be rejected. Oracle will raise an error and the invalid piece of data will be rejected. Oracle will raise and error and all current transactions will be rolled back. 8. (TCO 10) Which of the following commands is used to establish a user account? (Points : 4) CREATE NEW USER username IDENTIFIED BY password; CREATE USERNAME username IDENTITY password; CREATE USER username PASSWORD password; CREATE USER username IDENTIFIED BY password; 9. (TCO 10) Which command will grant a system privilege to a user? (Points : 4) GRANT systemprivilegeUSERNAME username GRANT PRIVILEGE systemprivilegeTO username GRANT systemprivilegeTO username GRANT systemprivilege ON username ******************************************************

  13. CIS 336 Final Exam 2 For more course tutorials visit www.tutorialrank.com Tutorial Purchased: 2 Times, Rating: B+ 1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) A relational DBMS provides protection of the _______________________ through security, control, and recovery facilities. (Points : 4) user network

  14. system database 3. (TCO 2) If a foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key is/are said to exhibit __________ integrity. (Points : 4) referential restrictive secondary redundant 4. (TCO 2) A field that consists of integer values is a _________ type field. (Points : 4) Date/Time Yes/No Memo Numeric 5. (TCO 3) The PK must uniquely identify each entity instance. A primary key must be able to guarantee unique values. It cannot contain nulls. This rationale belongs to ____. (Points : 4) Unique values Nonintelligent Preferably single-attribute Security complaint 6. (TCO 3) A table that is in 1NF and includes no partial dependencies only is said to be in_____________. (Points : 4)

  15. 1NF. 2NF. 3NF. 4NF. BCNF. 7. (TCO 3) A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in_____________.(Points : 4) 1NF. 2NF. 3NF. 4NF. BCNF. 8. (TCO 4) In Oracle, the DEFAULT constraint is used to ____________________. (Points : 4) define which column is the default primary key if one is not explicitly named define which column is the default foreign key if on is not explicitly named assign a value to an attribute when a new row is added and a value is not defined validate data when a value is added to an attribute Page 2 - Multiple Choice 1. (TCO 4) Which of the following is not considered a database object? (Points : 4)

  16. a table a view a constraint a sequence an index 2. (TCO 4) Which of the following SQL statements would you use to create a table named DEVRY? (Points : 4) CREATE Devry ( Student_id NUMBER (12), Student_name VARCHAR2 (15), Student_city VARCHAR2 (12) ); CREATE TABLE Devry ( Student_id NUMBER (12) Student_name VARCHAR2 (15) Student_city VARCHAR2 (12) ); CREATE TABLE Devry ( Student_id NUMBER (12), Student_name VARCHAR2 (15), Student_city VARCHAR2 (12) ); CREATE TABLE Devry Student_id NUMBER (12), Student_name VARCHAR2 (15), Student_city VARCHAR2 (12);

  17. 3. (TCO 5) The ____ constraint requires that a specific condition be met before a record can be added to a table. (Points : 4) UNIQUE REFERENCE CONDITION CHECK 4. (TCO 5) The ___________ model is both software and hardware dependent. (Points : 4) conceptual logical condensed physical 5. (TCO 6) The _________________________ statement can be used to enable or disable a constraint. (Points : 4) alter table modify table create table update table 6. (TCO 6) When using the ALTER TABLE...DROP COLUMN command, which of the following is not correct? (Points : 4) The command's effect on the table is permanent. You cannot delete the last remaining column in a table. The command deletes both the column and its contents. The command can be used to delete multiple columns from a table.

  18. 7. (TCO 6) Which of the following keywords can be used to change the size, datatype, and/or default value of an existing column? (Points : 4) ADD MODIFY CHANGE RESET 8. (TCO 7) Any type of subquery can be used in the _________ clause of a SELECT statement. (Points : 4) WHERE HAVING FROM ORDER BY GROUP Page 3 - Multiple Choice 1. (TCO 7) Using the BOOK_CUSTOMER and BOOK_ORDER tables shown below for references, which SQL statement will return the same results as the following SQL statement? SELECT * FROM book_customer, book_order; (Points : 4) SELECT * FROM book_order CROSS JOIN book_customer; SELECT * FROM book_order, book_customer WHERE book_order.customer# = book_customer.customer#; SELECT * FROM book_order, book_customer WHERE book_order.customer# >book_customer.customer#;

  19. SELECT * FROM o book_order , c book_customer WHERE o.customer# = c.customer#; 2. (TCO 7) If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be _______ rows in the results. (Points : 4) 8 13 5 40 3. (TCO 8) Based upon the contents of the BOOK_ORDER table shown below, which of the following SQL statements will display the ship date for order 1006 as April 2, 2002? (Points : 4) SELECT order#, TO_CHAR(shipdate, 'fmMonth DD, YYYY') FROM book_order; SELECT order#, TO_DATE(shipdate, 'fmMonth DD, YYYY') FROM book_order; SELECT order#, TO_CHAR(shipdate, 'Month DD, YYYY') FROM book_order; SELECT order#, TO_DATE(shipdate, 'Month DD, YYYY') FROM book_order; 4. (TCO 8) Based upon the contents of the BOOK_ORDER table shown below, which of the following SQL statements will display only those orders shipped to the zip code zone that begins with 323? (Points : 4)

  20. SELECT order#, SUBSTR(shipzip, 1, 323) FROM book_order; SELECT order#, SUBSTR(shipzip, 1, 323) FROM book_order WHERE ; SELECT order# FROM book_order WHERE (shipzip, 1, 323); SELECT order# FROM book_order WHERE SUBSTR(shipzip, 1, 3) = 323; 5. (TCO 8) Which of the following functions determines the number of characters in a character string? (Points : 4) COUNT NUMBER LENGTH DECODE 6. (TCO 9) Which of the following is not a valid SQL command? (Points : 4) UPDATE acctmanager SET WHERE ; UPDATE acctmanager SET (amname); UPDATE acctmanager SET (amname) WHERE ; UPDATE acctmanager WHERE ; 7. (TCO 9) Using the promotion table shown below, which of the following SQL statements will insert a new row into the PROMOTION table?

  21. (Points : 4) INSERT INTO promotion (gift, minretail, maxretail) VALUES (FREE BOOK, 75.01, 89.99); INSERT INTO promotion (gift, minretail, maxretail) VALUES ('FREE BOOK', 75.01, 89.99); INSERT INTO promotion VALUES (FREE BOOK, 75.01, 89.99); INSERT INTO promotion (gift, minretail) VALUES (FREE BOOK, 75.01, 89.99); 8. (TCO 10) A ____ is a uniquely identifiable object that allows a given person to log on to the database. (Points : 4) user role profile manager 9. (TCO 10) Which command will delete a user account from Oracle9i? (Points : 4) DELETE USER username; DELETE USER username IDENTIFIED BY password; DROP USER username IDENTIFIED BY password; DROP USER username; Page 4 - Essays 1. (TCO 2) What is Character data? (Points : 15) 2. (TCO 3) Explain the term "second normal form". (Points : 15)

  22. 3. (TCO 7) Sally needs to find all employees who have a date-of-hire between 01-JAN-2003 and 01-JAN-2005. She writes and executes the following query: 4. (TCO 8) In using group functions we cannot place a group function in a WHERE clause. Explain how the WHERE clause and HAVING clause are similar and why you can use the HAVING with a group function but not a WHERE clause. (Points : 15) 5. (TCO 6) Write the SQL statement that will remove the database table EMPLOYEE. (Points : 15) 1. (TCO 7) Write a join query using NATURAL JOIN that for every order in the ORDERS table will list the order number and order date along with the cusnumber, last name, and first name of the customer who placed the order. 2. (TCO 8) Write a query that will list the part number, part description, and on-hand value (units on hand * unit price) for each part in item class AP. Give the expression an alias of "On Hand Value". 3. (TCO 8) Write a sub query that will list the part number, part description, and units on hand of all parts where the number of units on hand is greater than the average of units on hand for all parts. 4. (TCO 7) Using the BETWEEN operator, write a query that will list all details about all orders that were placed during the period of September 3rd thru September 5th of 1998. 5. (TCO 7) Write a query that will list the part number and part description for all parts in the PART table. (Points : 15)

  23. ****************************************************** CIS 336 Final Exam 3 For more course tutorials visit www.tutorialrank.com Tutorial Purchased: 4 Times, Rating: A 1. (TCO 2) Explain what physical independence means. (Points : 15) 2. (TCO 8) Sally wants to query the EMP table and determine how many of the employees live in each of the cities the company has factories in. She writes the following query but it does not work. What is wrong with the way the query is constructed? 3. (TCO 3) Explain in what two specific cases presented in the text composite primary keys are particularly useful. (Points : 15) 4. (TCO 7) How are comparisons done on character string data in a queries WHERE clause, and why would this be especially useful in comparing certain attributes? (Points : 15)

  24. 5. (TCO 6) Oracle will not allow the changing of a column's data type unless the column is empty, and even then it may still be prohibited. Discuss why? (Points : 15) 6. (TCO 7) Write a join query using NATURAL JOIN that for every order in the ORDERS table will list the order number and order date along with the cusnumber, last name, and first name of the customer who placed the order. 7. (TCO 8) Write a query that will list the part number, part description, and on-hand value (units on hand * unit price) for each part in item class AP. Give the expression an alias of "On Hand Value". 8. (TCO 8) Write a query that will display the customer number, first name, and last name for every customer with a balance greater than $50. Display the first and last name as a single column and provide an alias for the column. 9. (TCO 7) Write a query that will list the customer number, last name, and first name for every customer represented by sales rep 03 or sales rep 12. 10. (TCO 7) Write a query that will list the customer first and last name and the sales rep commission rate using JOIN ON. ****************************************************** CIS 336 Final Exam 4 Sets of Answers

  25. For more course tutorials visit www.tutorialrank.com Tutorial Purchased: 4 Times, Rating: A CIS 336 Final Exam 4 Sets of Answers ****************************************************** CIS 336 Final Exam 4 For more course tutorials visit www.tutorialrank.com Tutorial Purchased: 3 Times, Rating: A+ 1. (TCO 7) For every order, list the order number, order date, part number, part description, and item class for each part that makes up the order. 2. (TCO 8) Write a query that will display the customer number, last name, and first name for every customer I whose first name begins with the letter "D".

  26. 2. (TCO 8) Write a query that will display the earliest date an order was placed. 3. (TCO 7) Using the BETWEEN operator, write a query that will list all details about all orders that were placed during the period of September 3rd thru September 5th of 1998. 5. (TCO 7) Write a query that will list the customer first and last name and the sales rep commission rate using JOIN ON. ****************************************************** CIS 336 Group Project For more course tutorials visit www.tutorialrank.com This project is designed to touch all aspects of the fundamental concepts of database design and logical data modeling covered during the class. Each student will be responsible for designing, developing, and demonstrating the functionality of a database created based on a set of business specifications that you create. At the end of the session, each student will submit a report that includes the logical and physical design of the database and demonstrates the database's ability to deliver meaningful reports. All aspects of the project will be covered during the

  27. week or weeks prior to a deliverable, and each task deliverable in the project will be supported through the exercises and labs done in the course. Guidelines You will create a proposal for a commerce business of your choosing. This can be an actual or fictional business that sells products and/or services. You will determine the business rules and report requirements. Entities that must be included in your model are the following. Customer Product (or Service) Order Project Deliverables This task is broken down into four deliverables. · Task 0 · Task 1 · Task 2 · Task 3

  28. Remember that this is a work in progress and in all likelihood, you will have to make numerous changes to your tables and schema as you go along. These changes are to be expected and are a normal part of the database design and implementation process. Please refer to the sections below for details regarding deliverables for each task. Task 0 The first task is to create your business narrative. Some of the questions that you should address include (but are not limited to) the following. · What is the name of your business? · customers)? Who will be the users of the system (managers, employees, · What product(s) and or service(s) does it sell? Be specific. · Who are the customers? What is the customer base? · What sales and distribution channels are used? · How are payments made? · Are products delivered or shipped to customers in some way? · Are sales reps involved? Are they paid on commission? · What type of reports are needed by the business?

  29. Please include as much detail as possible in order to provide a foundation for Task 1. Submit your proposal as a Word document. Note that this proposal may be revised in the coming weeks as your understanding of the database modeling process grows. Task 1 Develop a detailed logical model of your database based on your business scenario submitted for Task 0. Your data model will include an entity-relationship diagram (ERD) developed with Microsoft Visio or MySQL and a data dictionary (DD) developed with Microsoft Excel. Note that your deliverables each week include any revisions to the previous task. Submit your Task 1 deliverable as a zip file including the following. · Task 0 proposal (with any revisions or updates) · page Your ERD copied to a Word document that fits on a single 8 x 10 · Your DD (Excel file) Task 2 First, make sure to make any necessary revisions to your logical model based on Task 1 feedback. Using MySQL, write the statements to

  30. develop a physical model of your database by creating the tables and constraints based on your Task 1 logical model. Be sure to use your ERD and data dictionary and create all constraints appropriately. Once your tables are created, you will populate them with data using MySQL statements to insert records into the database. You should insert a minimum of five records into each table and more where necessary to ensure that the data in the database is sufficient to allow for each of your reports to be tested. The result will be a script file that includes statements to · DROP all tables; · CREATE all tables with constraints; · INSERT data into all tables; and · SELECT data from all tables to show the data in each table. Name this file yourname_task2.txt. Also create a Word document that shows the structure of each table and the data in each of your tables (copy and paste from MySQL). Name this file yourname_tables.docx. Note that your deliverables each week include any revisions to a previous task. Submit your Task 2 deliverable as a zip file including the following. · Task 0 proposal (with any revisions) · Your Task 1 ERD (with any revisions)

  31. · Your Task 1 DD (with any revisions) · (yourname_task2.txt) Your Task 2 script file to create and populate tables · (yourname_tables.docx) A Word document showing all tables and data Task 3 Your final task is to use your tables for an update and for reports. There are two requirements. 1. subquery. Make sure to prove that your update executed correctly by showing data in the tables before and after the update. You will demonstrate an update that includes two tables and uses a 2. include a join, one must use a subquery, and one must use an aggregate function. You need to describe the business requirement of the update and each report. Note that numeric PK values are not often meaningful to a person looking at the report. You will create a script file that has each of the queries called yourname_task3.txt. You will also document the results of this task in a Word document called yourname_reports.docx to show the result of each query. For the update and each report, You will also develop at least four meaningful reports. One must · describe the update and report requirement; · show the query; and

  32. · show the result of the query. Include a final section for Conclusions and Lessons Learned regarding the project.Note that your deliverables each week include any revisions to a previous task.Submit your Task 3 deliverable as a zip file including the following. · Task 0 proposal (with any revisions) · Your Task 1 ERD (with any revisions) · Your Task 1 DD (with any revisions) · revisions) Your Task 2 script file to create and populate tables (with any · A Word document showing all tables and data (with any revisions) · (yourname_task3.txt) Your Task 3 script file with update and reports · (yourname_reports.docx) A Word file with results of Task 3 and lessons learned ****************************************************** CIS 336 iLab 1 Normal Forms and Entity Relationship Diagrams For more course tutorials visit

  33. www.tutorialrank.com CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams Introduction In this exercise, you will analyze a de-normalized data set presented in the form of a spreadsheet. You will next construct a series of dependency diagrams, transforming the evolving data model from First Normal Form (1NF), to Second Normal Form (2NF), and finally to Third Normal Form (3NF). When the model has reached 3NF, you will construct the Entity Relationship Diagram (ERD) depicting the logical design of the database. Your ERD will use Crow’s Foot notation to denote the relationships between tables. Sample Exercise, With Solution Before completing your lab, please review this example problem. The diagram below is a partial depiction of a business spreadsheet for a retail store operation. The store sells books. Books have a 13-digit International Standard Book Number (ISBN), a title, a publisher, and a unit price. Invoices track sales of books. An Invoice contains one or many line items, with each line item reflecting the sale of one or more copies of a specific book. Every publisher has a company name, and a publisher code. InvNum ISBN13 InvDateBookTitlePubCodePubNameQtyunitPrice 1022 9781291940336 2015-03-09 MYSQL Functions 1001 Lulu.com 3 4.99 1022 9780321833877 2015-03-09 MySQL, Fifth Edition 1029 Addison- Wesley 5 33.95 1022 9781890774820 2015-03-09 Murach’s MySQL, 2nd Edition 1032

  34. Murach, Mike & Associates, Inc 2 48.95 1249 9781449374020 2015-02-22 MySQL Cookbook: Solutions for Database Developers and Administrators 1118 O’Reilly Media, Incorporated 9 50.59 1249 9781449325572 2015-02-22 PHP & MySQL: The Missing Manual 1118 O’Reilly Media, Incorporated 6 29.95 1249 9781890774790 2015-02-22 Murach’s PHP and MySQL, 2nd Edition 1032 Murach, Mike & Associates, Inc 1 48.95 BookSales   1. Using the BookSales table structure shown above, draw the dependency diagram, and show all dependencies, full, partial, and transitive. Figure 1 -solution 1  Discussion: A composite primary key consisting of InvNum + ISBN13 can be constructed, ensuring that all rows are unique. All remaining attributes are shown to be dependent (at least partially) on this composite key, so the table is 1NF. Further analysis shows that InvDate is dependent on only part of the key (InvNum), and that unitPrice, PubCode, and BookTitle also depend on part of the key (ISBN13). A transitive relationship is also revealed: PubName truly depends on PubCode, which in turn depends on ISBN13. 2. Using the answer to exercise 1, remove all partial dependencies and draw the new dependency diagrams. For each new table created, specify its normal form (1NF, 2NF, 3NF). Figure 2- solution 2 Discussion: To achieve 2NF, we must remove partial dependencies. This is done by decomposing into three tables, and three dependency diagrams at this stage. Notice that the Transitive relationship has not yet

  35. been addressed.   3. Using the answer to exercise 3, remove all transitive dependencies, and draw the new dependency diagrams. For each new or revised table, specify its normal form. Figure 3 – Solution 3 Discussion: The transitive relationship has been removed by creating the Publishers table, in which PubName is fully dependent upon PubCode. Pubcode also persists as a dependent attribute and Foreign Key in Books. All tables are now in 3NF, and have been given meaningful names reflecting the entities they represent. LINEITEMS represents the collection of line items for all invoices. Invoices represents the collection of Invoices for all customer orders. Books makes up the list of all books available for sale (whether they have ever been ordered or not). Each book has a publisher, and the publisher code and Name reside in the Publishers table.   4. Draw the ERD for exercise 3, using Crow’s Foot notation. End of Sample Exercise.   Student Exercise The student exercise for this lab is similar to the sample exercise presented above. Consider the following spreadsheet containing information about customers, their shipping and billing addresses, and the countries corresponding to each of those addresses. A customer may be associated with zero to many addresses. A customer may have 0 or 1 default billing address. A customer may have 0 or 1 default shipping address. Each

  36. address may be associated with 0 or 1 countries, while each country may be associated with 0 to many addresses. cust_idfnamelnamedef_bill_addr_iddef_ship_addr_idaddr_idstreet_addr city state zip country_codecountry_name 202 John Smith 1096 2039 1096 123 Happy Ave. Orlando FL 32801 76 United States 202 John Smith 1096 2039 2039 2024 Shorline Dr. Seattle WA 98101 76 United States 202 John Smith 1096 2039 8053 100 N. Kent Ave. St. James WA 34 Jamaica 175 Hilda Yeager 2172 2172 3879 48 Spatzel Ct. Dusseldorf 5111 29 Germany 175 Hilda Yeager 2172 2172 3921 162 Rue Moritz Paris 75001 28 France 321 Siri Apple 1881 1881 1881 1 Infinite Loop Cupertino CA 95014 76 United States Using the example solution as a guide, perform the following steps. 1. Using the CustomerAddressCountry spreadsheet show above, draw the dependency diagram, and show all dependencies, full, partial, and transitive. Paste your solution into the answer sheet, and label it, Answer #1. 2. Using the answer to exercise 1, remove all partial dependencies and draw the new dependency diagrams. For each new table created, specify its normal form (1NF, 2NF, 3NF). Paste your solution into the answer sheet, and label it, Answer #2. 3. Using the answer to exercise 3, remove all transitive dependencies, and draw the new dependency diagrams. For each new or revised table, specify its normal form. Paste your solution into the answer sheet, and label it, Answer #3.

  37. 4. Draw the ERD for exercise 3, using Crow’s Foot notation. Paste your solution into the answer sheet, and label it, Answer #4. ****************************************************** CIS 336 iLab 2 The Expanded Entity Relationship Diagram For more course tutorials visit www.tutorialrank.com CIS336 Lab 2: The Expanded Entity Relationship Diagram LAB OVERVIEW Scenario and Summary This lab introduces the next step in creating a data model, the Entity Relationship Diagram (ERD). You will be given a business scenario for a University Medical Center, which is a small community hospital. The business specifications will outline a number of things about the business, some of which will apply directly to the database you are being asked to model. There is a table that lists the entities (tables) that will be needed for the database and related attributes (columns) for each entity. There is also a column that lists specific information about the entity that will be helpful in determining its relationship to other entities within the model. Be sure to include the minimum and maximum occurrences of each

  38. relationship (cardinality) and to supply a name to the relationship that will work in both directions. Make sure to use Crow’s Feet notation in your ERD. Narrative/Case Study The University Medical Center is a small, community hospital. A new hospital administrator has recently been hired by the Board of Directors, and directed to right-size patient care and pharmacy services and improve profitability. The hospital operates three clinical facilities: the main hospital, a mid-town clinic, and an Urgent Care location. The hospital also offers selected in-home care services. Many of the patients are repeat or regular patients who receive regular treatment for various conditions, and many utilize the hospital’s pharmacy services for prescription medications. The pharmacy dispenses about 3,000 different prescription medications of various kinds. Every prescription is associated with one patient, and is logged by the dispensing clinic. The new administrator wants to know which drugs are most prescribed, and also which are the most profitable. The following is some general information about the organization and its current processes. • The hospital operates three clinical facilities. • A healthcare worker logs in at a facility at the start of a shift and logs out at the end. • The name, address, Social Security number and other information is recorded for every healthcare worker. • All healthcare workers have one billing rate, which is determined by their job description. • Each medication dispensed by prescription is linked to both the prescription number and the medication ID number, recording the item price and the quantity dispensed. As a convenient and affordable means of providing hospice care,

  39. palliative care, and convalescent care to patients that need occasional/intermittent skilled nursing, but do not require hospitalization, the hospital offers limited in-home care. Recording of in-home care includes the healthcare worker ID, their departure time and return time, and also the prescription ID for any prescriptions administered by the healthcare worker in the home setting. The hospital administrator would like to know what home-care visits have been made to whom, by whom, when, and how long they took. There is concern at this point that the cost of providing limited home healthcare is not providing adequate return on investment, and the program should be revised or discontinued. Requirements You have been asked to develop a logical data model for University Medical Center based on the information given to you by the new hospital administrator and their staff. Through analysis of the nouns and verbs in the case study above, you have accumulated the following entity, attribute, and relationship information shown in the table below. The attribute list may not be complete. If you determine that additional attributes are needed to better define an entity, then you should add them. Entities Attributes and Relationships for University Medical Center (Parallel Lab Exercise): Entity Attributes Relationships HealthcareWorkerHealthcareWorkerID, LastName, FirstName, SSN, Address, City, State, Zip, Phone Number, HealthcareWorkerTypeID A healthcare worker can belong to any one of the three job categories, but can belong to one and only one of the three. healthcare worker has names and other contact information. HealthcareWorkerTypeHealthcareWorkerTypeID, HealthcareWorkerTypeDesc, HourlyBillingRate A healthcare worker can be either a physician (diagnoses, prescribes medication), nurse

  40. (provides physician-ordered treatments, administers medications), or a pharmacist (dispenses, delivers medication). ClinicLogClinicLogID, ClinicID, Login, Logout, HealthcareWorkerID Patients may be treated by a healthcare worker at a clinic and can be tracked by the clinic log number. A healthcare worker must sign into the clinic before he or she can serve patients, and must sign out when finished treating patients at that clinic. A healthcare worker may serve portions of a shift at more than one clinic. Clinic ClinicID, ClinicLocationDesc, AMAAccredNum The hospital operates three clinical facilities: General Hospital, Midtown Clinic, and Urgent Care. InHomeCareInHomeCareID, HealthcareWorkerID, PrescriptionID, DepartTime, ReturnTime. Relates to both the healthcare worker and the prescription entities. This entity will help track provision of home healthcare. A healthcare worker can provide many home visits but a prescription is administered on a home visit by one and only one healthcare worker. Method MethodID, Method Description Relates to medication and identifies the method of administration, for example, oral, injection, and so on. Medication MedicationID, MedicationName, Dosage, Cost, QuantityOnHand, LastPurchasedDate, ReorderMinimum Identifies the medication the hospital pharmacy dispenses. One or more medications can be dispensed per prescription. The quantity on hand allows Pharmacists to determine the inventory levels. The reorder minimum can be used to determine when the inventory level has reached a reorder point. Prescription PrescriptionID, MedicationID, BillingAmount, TransactionDateTime, ClinicLogID, PatientID A prescription is identified by a single prescription order. Prescriptions are

  41. • made to one or more patients but only one patient at a time; • made by one or more pharmacists but only one pharmacist per prescription; • recorded on one or more clinics but only one clinic per prescription; and • administered by one or more nurses but no one prescription can be administered by more than one nurse. PrescribedMedicationPrescriptionID, MedicationID, ItemPrice, QuantityDispensed Prescribed medication is part of a prescription and records medication dispensed per prescription. Prescribed medication must be able to associate multiple medications sold on a single prescription number. Patient Patient Number, First Name, Last Name, Address, City, State, Zip, Phone Number A patient can be associated with multiple prescriptions, but any one prescription is to one and only one patient. A prescription can occur without a patient registering in the system (e.g., an unconscious patent arrives by ambulance in the emergency room and receives life-saving emergency treatment). Using an appropriate drawing/data modelling tool, develop an ERD that meets the following guidelines. • Draw the entities with their attributes. • Indicate the relationships between the entities using Crow’s Foot notation. You will need to determine the cardinality and optionality for each direction of the relationships. Some of the Foreign Key relationships are identified in the graph above but not all. Be sure you identify and account for all Foreign Key relationships. • Add a name (in both directions) to the relationships. Remember, if you can verbalize the relationship in both directions, then you probably have a valid relationship. Deliverables

  42. The deliverable for this lab will be your completed ERD as a single MS Word document using copy/paste or imported as an image from your drawing/modelling application, cropped and sized appropriately (it should fit on a single page), and named lab2_solutions_yourname. LAB STEPS STEP 1: Drawing Entities and Attributes Back to Top Be sure to include all of the entities that have been defined. You need to include at least the primary and foreign key attributes where applicable in your diagram. STEP 2: Add Relationships Back to Top Be sure that you link all entities based on PK to FK relationships. There may be a case where you need to identify a combination PK and if so make sure that all of the relationships involved are defined. Be sure that you have set your drawing/modelling tool set to show Crow’s Foot notation. Also, be sure that you are defining the correct cardinality for the relationships. STEP 3: Naming of relationships Back to Top If your drawing/modelling tool creates a default name for relationships, do not simply accept this default without due consideration. There may be some relationships where the default is applicable, but in most cases you will want to explicitly name the relationship. Be sure that you have provided a verb phrase for both directions in the relationship. This is the end of lab #2 ******************************************************

  43. CIS 336 iLab 3 Building the Physical For more course tutorials visit www.tutorialrank.com CIS-336: Lab 3: Building the Physical LAB OVERVIEW Scenario and Summary The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file titled CIS336Lab3Files from Doc Sharing. This zip file contains the ERD, Data Dictionary, and test data for the tables you create as you complete this exercise. Your job will be to use the ERD Diagram found below as a guide to define the table structures and constraints using both CREATE TABLE and ALTER TABLE statements. Once this has been done, you will need to write the INSERT INTO TABLE statements to insert the data provided into the table. The data

  44. should verify that the constraints you have created are valid and define the correct referential and data integrity constraints asked for. Lastly, you will write SELECT statements to query the tables and verify the data was populated. Please use exactly the data provided, without addition, deletion, or alteration except as directed, as your results may be evaluated against expected results generated using this exact data set. Narrative/Case Study For this lab, you will be creating SQL statements to build a series of relational tables, using SQL CREATE statements in a script file format for the Student Database. You will then populate those tables through the use of INSERT statements with sample data. You will need to create a script file and name it YourName_Lab3.txt containing the following code. 1. The drop table statements listed later in the specifications of this lab. 2. The CREATE TABLE statements required to build the six tables. 3. The INSERT statements necessary to insert all of the sample data. 4. Six select statements to verify that the data is in the tables and accessible.

  45. To help you accomplish this task successfully, you are being supplied with the ERD Diagram which follows, and the exact data to be inserted into each table, which may be found via the Doc Sharing tab on the course website. The following guidelines are being provided to help assist you in creating your script file. · Use the names for the tables and columns as listed in the ERD. Do not change them as it will affect your grade. · Creating Constraints o Create all NOT NULL constraints as indicated in the ERD. o Create all PK constraints as indicated in the ERD. o Create all FK constraints as indicated in the ERD. · Create all of the tables and all of the constraints before populating any of the tables with data. · Because FK constraints will be in place when the insert statements are executed, you will need to consider carefully which tables must be created before others in order to ensure that FK constraints are not violated.

  46. · The COURSE table has a self-referencing FK constraint. Specifically, some courses have prerequisite courses. Consequently, the record for a course possessing a prerequisite course cannot be successfully inserted into the table unless the record for the prerequisite course has already been inserted. This may require you to reorder the insert statements to resolve FK violations when loading the table. You may reorder the data provided for this table, but do not alter it. · The data for one table intentionally contains a record containing an FK constraint that is not resolved by a record in the parent table. This orphaned record has been included as an exercise for you to find. Because this record has an unreconciled FK constraint, it cannot be successfully inserted. You will need to delete or comment out the insert statement for this one record in order to produce a script that runs without errors. · Aside from reordering the data for the COURSE table as necessary, and commenting

  47. out/deleting the ONE record whose FK dependency cannot be resolved by the data provided, you are NOT to modify, add to, or delete from the data provided. Your SQL script must produce tables containing data identical to the expected solution set, or points will be deducted. · ALL character strings must be enclosed in single quotes. This includes alpha strings and alphanumeric (remember that any formatting within a numeric string makes it alphanumeric). · If you are inserting a NULL, do not enclose the word NULL in single quotes, as this will insert the word NULL into the row. To insert a null you simply use the word NULL. Deliverables The deliverable for this lab will include the following documents. 1. Your script file. Create this file in Notepad, or another PURE TEXT editor—NOT Word. Make sure your name is in a comment area at the top of the script file. Use a double dash to create a one- line comment. –Jane Smith

  48. –Lab 3 2. Your script file must execute without error. It is recommended that you begin early in the week, and post any questions to the Q & A discussion in order to produce a working script by the due date. Be sure your name is on all documents and that all documents have been included in a single zip file for this week’s assignments. Refer to the following ERD in constructing your solution. Student PKStudent_ID Salutation First_Name Last_Name Street_Address Phone Employer Registration_Date FK1Zip

  49. Enrollment PK,FK1Section_ID PK,FK2Student_ID Enroll_Date Final_Grade LAB STEPS STEP 1: The DROP Statements A DROP TABLE statement must appear in your script file, prior to the SQL statements for creating the table in question. This will allow you to run and re-run your script file as often as you need to. The very first time you run your script the table does not exist, so the IF EXISTS clause causes the statement to be ignored. Thereafter, the table will be deleted, ensuring that your CREATE TABLE statement creates the table fresh and clean, with only the attributes present in the current revision of the CREATE statement. Here is an example of one of the six DROP TABLE statements you will need to create. DROP TABLE IF EXISTS `ENROLLMENT` ; STEP 2: The CREATE TABLE Statements

  50. Next, define the CREATE TABLE statements for the six tables that you are to create based upon the ERD (provided above) for this lab. Be sure to follow the guidelines given above on how and where to create the different types of constraints for each table. This will include PK, FK, and NOT NULL constraints. STEP 3: The INSERT Statements for the Data The third step is to create the insert statements to insert the sample data into the tables created in Step 2. The data for each table is contained in text files, named for the table whose data it contains. Modify the format of the data (e.g., date formats and add or eliminate quote marks) as needed to craft your insert statements, but do not change the inherent value of the data. STEP 4: The SELECT Statements The next step of the lab will be to create the select statements to verify the data was inserted correctly. You should have six select statements; one for each table. The command is SELECT * FROM Table_Name; For example, to select all columns from the Student table, the command would be

More Related