NOTE: This is an assignment from one of my clients. You can choose any business application (ex. Human Resources Database). All requirements below must be completed for his grade to be given. Must be completed using Oracle.
The project is to design and implement a database that is able to support a business application that will meet the requirements of a particular business or organization, real or hypothetical. You will analyze the organization's data requirements, design an appropriate database, and implement the database using Oracle. You may also choose an e-business website, study the data needed to support the e-business and derive the database based on your understanding of the e-business.
Project report should be organized as follows:
Describe the business for which you design the application.
Describe the part of business operation your database supports.
Describe the advantages of automating this operation.
Part2: Database analysis and design
Describe the data requirements
ERD and database schema
Requirements: The ERD must have at least four entity types and at least one entity type is a supertype with subtypes. It should also have at least one multi-valued attribute and one attribute of a relationship.
Part 3: Implementation
Use SQL statements to create tables and insert records. Each table should have reasonable number of fields and have around 10 records enough for testing SQL Select statements later. You should demonstrate with the Create Table commands in enhancing the following integrity constraints:
a. Primary key constraints: no duplication and not allowing null value
b. Default value for a field
c. Enforcing domain constraint
d. Enforcing referential integrity
Part 4: SQL Statements
Attach a printout of the SQL statements you used for creating the database.
Based on the database you created in project 1, define queries as specified below:
1. Define a query that uses the Natural Join command to join three tables.
2. Define a query that uses the traditional join method (the one that use the WHERE clause to specify the key and foreign equal) to join two or three tables.
3. Define a query to perform an Outer Join. It is your choice to do a Full, Left, or Right Outer.
4. Define a query that uses the Union operator to retrieve some useful information.
5. Define a query that uses the Intersect operator to retrieve some useful information.
6. Define a query that computes subtotals from the results of a join operation.
7. Define a query that uses the HAVING clause to check the subtotals.
8. Define a query with a subquery.
9. Define a query that uses the IN (or NOT IN) to check keys returned by a subquery.
10. Define a query with a correlated subquery.
11. Define a query that uses the EXISTS (or NOT EXISTS) to check whether any records are returned by a correlated subquery)
12. Define a query with a calculated field.
*Requirements: For each query you defined:
a. Briefly explain the purpose of the query.
b. Submit the SQL statement and the results.
I will pass along any questions to the client.