CST2355_A4
doc
keyboard_arrow_up
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
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