This project includes the tasks in database normalization, enforcing data consistency, transaction processing, and design and implementation of BSON database
Tasks
Step 1: Database normalization
This task requires access to either csora Oracle 19c database server or Oracle 19c running on a virtual machine.
Consider the following conceptual schema of a sample database domain where the employees work at the departments distributed of over many locations The employees are supervised by other employees. Please see a conceptual schema below.
SQL script dbcreate.sql can be used to create the relational tables implementing a sample database. SQL script dbdrop.sql can be used to drop the relational tables.
Use information provided in a conceptual schema above and in the contents of SQL script dbcreate.sql to discover the functional dependencies valid in each relational schema (header of a relational table). Next, use the functional dependencies found to derive the minimal keys and to find the highest normal form valid for each relational schema.
If you find that a relational schema is not in BCNF then transform the relational schema into BCNF. An objective is to minimize the total number of relational schemas in BCNF.
Save the functional dependencies found, derivations of minimal keys, identifications of the normal forms and potential transformations of relational schemas in a file solution1.pdf. The file must contain all functional dependencies discovered, all derivations of minimal key and justifications for highest normal form valid in each schema.
If you find that the transformations of the relational schemas into BCNF are needed then improve a script dbcreate.sql such that all relational tables created through processing of the script are in BCNF. Again, please keep in mind that an objective is to have the smallest number of relational tables in BCNF.
Append at the very end of a script dbcreate.sql SQL statements that insert data into the relational tables created in the previous step. Insert in to the database information about one department located at two addresses and two employees. One employee must supervise the other employee.
Process an upgraded script dbcreate.sql and save a report from processing in a file
solution1.lst.
Your report must include a listing of all SQL statements processed. To achieve that put the following SQLcl commands:
SPOOL solution1
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.
Deliverables
A file solution1.pdf with analysis of functional dependencies, derivations of minimal keys, identifications of the valid normal forms and transformations (if any) of the relational schemas into BCNF whenever it is necessary. A file solution1.lst with a report from processing of a script dbcreate.sql. A report must have no errors and it must list all SQL statements processed.
Step 2: Enforcing data consistency
A derived attribute is an attribute in a relational table such its values can be computed from the contents of the same or other relational tables in the same database.
For example, an attribute age is a derived attribute because an age can be computed from a date of birth and the present date (sysdate).
Another example of derived attribute is a total number of students enrolled in each course offered by a university. A total number of students per course can be computed by grouping a relational table ENROLLMENT over a course code and by application of an aggregation function COUNT()to each group.
Yet another example of derived attribute is an attribute income computed by deduction of a tax amount from a taxable income.
Implement SQL script file solution2.sql that performs the following actions.
- First, the script creates a derived attributein one of the relational tables created and loaded with data in the previous step. A derived attribute is up to you.
- Next, the script sets the values of a derived attributecreated in the previous step such that the values are consistent with the contents of the database.
- Next the script implements the mechanisms that keep the values of a derived attributeconsistent with the contents of with the database. A value of derived attribute must be automatically changed whenever insertion/deletion/update operation is performed on the contents of the database. You can either use a technique of stored PL/SQL or data base triggers.
- Finally, the script comprehensively tests a solution implemented in step (3).
When ready, process a script solution2.sql and save a report from processing in a file solution2.lst.
Your report must include a listing of all SQL and PL/SQL statements processed. To achieve that put the following SQLcl commands:
SPOOL solution2
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.
Deliverables
A file solution2.lst with a report from processing of a script solution2.sql. A report must have no errors and it must list all SQL statements processed.
Step 3: Transaction processing
Implement a stored PL/SQL procedure that increases one of the values of a derived attribute created in the previous step. The procedure must be implemented in a way that requires its execution at an isolation level serializable. When processed at an isolation level read committed the procedure would corrupt a value of a derived attribute.
Explain why a stored procedure implemented by you cannot be processed at an isolation level read committed. As an explanation provide a sample concurrent processing of two transactions both running at an isolation level read committed and such that a value of a derived attribute is corrupted. Use a two-dimensional visualisation of concurrent execution of database transactions as it has been already used in the lectures slides and Assignment
- Save your explanations and sample concurrent processing of database transactions that operate on a value of a derived attributein a file pdf.
When ready, create a file solution3.sql and save implementation of a stored PL/SQL procedure created earlier in the file. Next, process a file solution3.sql and save a report in a file solution3.lst.
Your report must include a listing of all SQL and PL/SQL statements processed. To achieve that put the following SQLcl commands:
SPOOL solution3
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.
Deliverables
A file solution3.lst with a report from processing of a script solution3.sql. A report must have no errors and it must list all SQL statements processed.
A file solution3.pdf with the explanations why a stored procedure implemented by you cannot be processed at an isolation level read committed and with a sample concurrent execution of two transaction, each one implementing the stored procedure and corrupting a value of a derived attribute at an isolation level read committed.
Step 4: Design and implementation of BSON database
This task requires access to MongoDB 4.2 database server available on a virtual machine.
Consider a conceptual schema given in Step 1 and below.
An objective of this task is to create a new collection of documents company that contains information represented by a conceptual schema given above and such that the collection is validated with JSON schema validator.
Use a method createCollection() to create a collection of documents company and use JSON schema validator to enforce appropriate nested structures of the documents to be included in the collection. All data types of valuates associated with the keys in the documents are up to you.
Next, insert into a collection company the same information as in Step 1 (information about one department located at two addresses and two employees; one employee must supervise the other employee) . Next, insert a document that fails a validation of only one of the constraints listed above. Add a comment with information why a document fails a validation.
Write your solutions into a file solution4.js.
When ready create a report from processing of solution4.js in the following way.
Use gedit editor to open a file solution4.js.
Select the entire contents of the file and Copy it into a buffer.
Open a new Terminal window and start mongo client in the following way.
mongo –port 4000
Paste the contents of the buffer copied earlier from gedit window in front of > prompt of mongo client. You may have to press Enter key to process the last query in a case when it is not followed by a newline control character.
Select the entire contents of the Terminal window and Copy&Paste it into a file solution4.lst. Save a file solution4.lst. Examine the contents of a file solution4.lst and make sure that it does not contain any errors.
Deliverables
A file solution4.lst with a report from processing of MongoDB script solution4.js with an implementation of the actions listed above.
