CST2355_A4

doc

School

University of Ottawa *

*We aren’t endorsed by this school

Course

2019

Subject

Industrial Engineering

Date

Jan 9, 2024

Type

doc

Pages

15

Uploaded by SargentGrasshopper3000

Report
1 CST2355 - Database Systems Assignment #4 Redesign [15%] This assignment relates to the following Course Learning Requirements: CLR 2 - Develop Advanced Database Design and Normalization CLR 3 - Develop advanced subjects and techniques of using the SQL database language CLR 4 - Use Oracle Procedural programming language (PL/SQL) to write programs that contain SQL statements CLR5 - Develop advanced Database Queries CLR6 - Understand and create users, privileges, and roles a dhere to the Academic Integrity policy - no sharing, no copying, no group work provide ONLY your own work. Background The company GlyphParts was started by a group of engineers that bought and resold salvaged auto parts. When they started they tracked their data solely with spreadsheets. Once those speadsheets became cumbersome they moved those spreadsheets to QuickBooks to better manage their data and operations. There was a point however where QuickBooks didn't meet their needs. It had limitations for reporting and integration. So all information was exported out of QuickBooks and imported into a Microsoft Access database with little consideration for data integrity and design. After three years, the Access database reached its size limitation. It was clear now that the company needed a better database with better performance, security, and significantly more disk space. They decided to assign you the task to reverse engineer their existing Access database and create a new database where they will now house their data.
2 Access Database Design This Access database has four major design issues: The data is not normalized and fields are duplicated There are minimal constraints on the data Table structures are missing primary and foreign keys The structure of the data doesn’t reflect how the business communicates with each other nor operates Pre-Assignment Instructions 1 -- Read Modules 10 and 11 on Brightspace 2 -- Review videos 18, 19, 20, 21 and 22 under the Additional Resources folder on Brightspace 3 -- Confirm Oracle Data Modeler, Oracle Database, SQL Developer, SQLPLUS are installed 4 -- Review the scripts provided with this assignme nt Embed all screenshots in the document where requested. For full marks screenshots must have formatted output and complete showing ONLY the information requested. Include multiple screenshots if needed.Clear your screen using the CLEAR SCREEN command and use the CST2355_A4_Format.SQL script to beautify your query results.
3 Instructions SQLPLUS and SQL Developer each have their own connection to the database. They cannot see each other’s changes without you running COMMIT. If either tool hangs, try running COMMIT in both tools. If hanging still occurs, close both tools and reopen them. The most common activity you will be a part of is redesign. You’ll be working on something already developed where it ran over-budget and several corners were cut. Not to mention the poor assumptions made on how the business and the technology works. You will need to redesign the Access design to fix the main problems after the fact. The Managers table in the MS Access database diagram is a list of managers and employees who report to those managers. A manager is duplicated for each employee that reports to them. A manager is also duplicated as an employee since they too will have their own manager. Each employee reports to only one direct manager. The Sales table is a list of the products sold by which sales people to which customers. A vendor sells the product to the company. The sales person resells the product to the customer. The OperationalExpenses table is all expenses costing the company money. It is a mixture of vendor payments and employee wages. Management uses the wage data for their budgeting. Payments and wages need to be separate entities. The AS-IS Data Model specifies how the business uses the data. To start a redesign, you start with the current state of the business and how they currently use the business. To start the data model, you use the business requirements. 1 -- Create a AS-IS Logical Model using Data Modeler with the Information Engineering Notation . Make labels viewable. Add the entities, ID attributes, 1:N relationships, and relationship labels that meet the following requirements. Hide all keys and ID attributes. manager monitors employee manager is a type of employee (do not add a manager entity) sales person facilitates purchase sales person is a type of employee (do not add a sales person entity) customer requests purchase purchase includes product vendor sells product employee submits payment payment made to vendor employee receives wage 2 -- Review the Access Design diagram. Add each field as an attribute in the appropriate entity for the AS-IS Logical Model . You may need to rename the attribute so it best matches the entity it’s in. For example: When adding from SALES, Product would be ProductName; RecipientLastName would be LastName; and TotalPrice & PaymentDate would be PurchasePrice & PurchaseDate. Leave all datatypes as unknown . Please note -- - 6 first/last name fields will be represented as first/last name attributes in EMPLOYEE - 5 address fields with be represented as one Addresses attribute
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4 - the Addresses attribute is added to CUSTOMER, EMPLOYEE, and VENDOR - Payment columns in OPERATIONALEXPENSES become payment named attributes in PAYMENT - Payment columns in OPERATIONALEXPENSES become wage named attributes in WAGE - add the PricePerUnit attribute to PRODUCT 3 -- Review the relationships in the AS-IS Logical Model . Confirm the correctness of each relationship’s min/max cardinalities based on the entities involved. Specify the identifying attributes for each entity as datatype identifying . You may need to add to the entity the identifying attributes of some of its parents. In some cases not all parents. For instance, employee attributes aren’t used to identifying purchases. Once done move identifying attributes to the top of the entity. Confirm the correctness of each identifying and non-identifying relationship. Layout your diagram so it reads left to right, top down. Add your name, student number, and the current date at the bottom right corner then provide a screenshot from Data Modeler of your AS-IS Logical Model . The business is taking the opportunity to specify new requirements. You will use these requires to expand on your AS-IS conceptual data model. This expanded model is your TO-BE Data Model. 4 -- Save your AS-IS Logical Model in Data Modeler . Then save your model with a different name to create your TO-BE Logical Model Add to the TO-BE Logical Model the entities, attributes and ID attributes that meet the following requirements. Hide all keys and ID attributes. department supported by employee department data : department name marketing department is a type of department (do not add a marketing department entity) price renegotiation initiated by marketing department price renegotiation accepted by vendor price renegotiation involves assessment of product price renegotiation approved by sales person
5 price renegotiation data : department name, vendor name, product name, sales person first name, sales person last name, approval date, original price, vendor price, approved price 5 -- Add to the TO-BE Logical Model the 1:N relationships, and relationship labels based on the requirements. Confirm the correctness of each relationship’s min/max cardinalities based on the entities involved. 6 -- Specify in the TO_BE Logical Model the identifying attributes for each entity by setting the attribute’s datatype to identifying . You may need to add to the entity the identifying attributes from its parents. Confirm the correctness of each identifying and non-identifying relationship. Layout your diagram so it reads left to right, top down then provide a screenshot from Data Modeler of your TO-BE Logical Model . The next steps include forward engineering your Logical Model to a Relational Model. Then forward engineer the relational model into code and run it on the destination database. These steps were done by another team. They handed you a script that generates the new database tables along with copies of the three MS Access tables. You will use these IMPORT tables to insert data into the EMPLOYEE destination table. 7 -- Run the CST2355_A4_Create_DB.SQL to create you IMPORT and destination tables. Create an INSERT INTO EMPLOYEE statement to insert the data found in the IMPORT_MANAGERS employee columns. Do not insert the manager column data. Assume the EmployeeType is FullTime Add a comment before your statement that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Run COMMIT . Provide a screenshot from SQLPLUS of your script with its results.
6 8 -- Create a UPDATE EMPLOYEE SET ManagerID statement to update the ManagerID for an employee using the IMPORT_MANAGERS manager name columns. To achieve this use a subquery that returns the correct EmployeeID for the managers first and last name. Add a comment before your statement that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Run COMMIT . Provide a screenshot from SQLPLUS of your script with its results. 9 -- Create an INSERT INTO EMPLOYEE statement to insert the data found in the IMPORT_SALES table. Assume the EmployeeType is Sales and the Title is Sales Person . Do not insert employees that already exist in EMPLOYEE using the IN operator. Add a comment before your statement that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Run COMMIT . Provide a screenshot from SQLPLUS of your script with its results.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
7 10 -- Create an UPDATE EMPLOYEE SET EmployeeType = 'Sales' statement to update all employees in EMPLOYEE that are also found in IMPORT_SALES . Do not use first and last names to do this. Add a comment before your statement that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Run COMMIT . Provide a screenshot from SQLPLUS of your script with its results. After importing your data, it is always a good idea to confirm the there is no duplication. Correlated queries are used to identifying minor changes in the data to rule out any potential duplicates. Duplicates can be caused by typos, name changes, and other human errors. 11 -- Create two correlated queries. Create the first one using EMPLOYEE that returns employees having the same last name but different first names. Create second correlated query using PURCHASE that returns the same products sold at the same price on the same date to the same customer (same CustomerID ) but have different PurchaseIDs . Add a comment before your SELECT statements that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Provide screenshots from SQLPLUS of your script with its results.
8 First query: Second query: When doing redesign, constraints are not added to the tables until all data is imported and cleaned up. To confirm that the constraints will be successfully created you need to check for data integrity. Invalid foreign key checks are used to identify parents that don’t have children and children that don’t have parents. Once you know which rows have incorrect data you can correct them so the constraints are successfully corrected. 12 -- Create two invalid foreign key checks. Create the first one that returns employees that work for a department that doesn’t exist. Create the second check that returns departments that don’t have employees. Add a comment before your SELECT statements that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Provide screenshots from SQLPLUS of your script with its results.
9 First invalid FK check: Employees not linked to any departments Second invalid FK check: departments without employees Often unconstrained data from old databases have poor field validation .That means column values likely do not have proper values. Some values shouldn’t have spaces, may be required to have a certain number of digits, or be in a certain format like phone numbers and postal codes.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
10 13 -- Review the CST2355_A4_REGEXP_LIKE.SQL . Use REGEXP_LIKE to create two invalid value checks. For the first one create a one SELECT that returns employees that don’t have all alphabetical characters in their first or last name. For the second check create one SELECT that returns purchases that have -- - no amounts (NULL) - amounts with more than 5 digits before the decimal - amounts with more than 2 digits after the decimal Add a comment before your SELECT statements that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Provide screenshots from SQLPLUS of your script with its results. First invalid value check: Second invalid value check: Once data has been imported and cleaned in the database, you add constraints to your tables to enforce data integrity.
11 14 -- Create a new script. Add ALTER statements to add primary key constraint to the parents of PURCHASE . Add ALTER statements to add foreign key constraints for PURCHASE and its parents. Add a comment at the start of your script that includes your name, student number, and today’s date. Copy and paste to run your script in SQLPLUS. Provide screenshots from SQLPLUS of your script with its results. Now that the data is successfully constrained you will create permissions so users can CREATE a new table, select from EMPLOYEE and update EMPLOYEE. 15 -- Go to SQLPLUS using SQLPLUS / AS SYSDBA . Run @CST2355_A4_Create_Users.SQL to create user ASSIGN_4X , user ASSIGN_4Y , and user ASSIGN_4Z . Clear your screen. Connect as ASSIGN_4X by running CONNECT ASSIGN_4X/CST2355db Run CREATE TABLE ASSIGN_4Z.TABLE_X (TestColumn INT); Run SELECT * FROM ASSIGN_4Z.TABLE_Z; Examine the code in the CST2355_A4_Create_Users.SQL script and locate the grant statements that allows the SELECT but doesn’t allow the CREATE. Connect as ASSIGN_4Y by running CONNECT ASSIGN_4Y/CST2355db Run CREATE TABLE ASSIGN_4Z.TABLE_Y (TestColumn INT); Run SELECT * FROM ASSIGN_4Z.TABLE_Z;
12 Examine the code in the CST2355_A4_Create_Users.SQL script and locate the grant statements that allows the CREATE and doesn’t allow the SELECT. Clear your screen. In SQLPLUS use CONNECT, GRANT, and UPDATE so you use the ASSIGN_4Z user to run an UPDATE EMPLOYEE to change the first name from Bob Jr to Bob JR . Do not use the ALL PRIVILEGES permission. Add a comment that includes your name, student number, and today’s date. Provide your screenshots from SQLPLUS. Connect / AS SYSDBA and run script:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
13 Connecting as ASSIGN_4X: Connecting as ASSIGN_4Y: Connecting to SYSDBA and Granting ASSIGN_4Z permission to update EMPLOYEE table
14 Updating EMPLOYEE table as ASSIGN_4Z: Confirm all screenshots are correct and not clipped. Save your assignment as a PDF and submit it in the assignment submission folder.
15 Assignment 4 Rubric Criteria Accomplished Developing Incomplete 1 points 0.5 point 0 points 1 -- AS-IS Data Model - Entities & Relationships Completed all instructions and submitted correct results Completed all instruction but submitted incorrect results Not per instructions, incomplete, or omitted 2 -- AS-IS Data Model - Attributes Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Not per instructions, incomplete, or omitted 3 -- AS-IS Data Model - Identifying Attributes Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Not per instructions, Incomplete or omitted 4 -- TO-BE Data Model - Entities and Attributes Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Not per instructions, Incomplete or omitted 5 -- TO-BE Data Model - Relationships Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Not per instructions, incomplete, or omitted 6 -- TO-BE Data Model - Identifying attributes Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Not per instructions, Incomplete or omitted 7 -- Import from IMPORT_MANAGERS Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 8 -- Import from IMPORT_MANAGERS (ManagerID) Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 9 -- Import from IMPORT_SALES Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 10 -- Import from IMPORT_SALES (EmployeeType) Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 11 -- Correlated Queries Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 12 -- Invalid Foreign Keys Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 13 -- Invalid Values Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 14 -- Data Integrity - EMPLOYEE & PURCHASE Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted 15 -- Permissions Completed all instructions and submitted correct results Completed all instructions but submitted incorrect results Doesn't compile, not per instructions, Incomplete or omitted
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help