Assignment
Objectives:
• Analyze and clearly describe the supplied dataset
o Use SQL Developer "import" function to import the supplied dataset for analysis
o Use various analysis techniques to assist in understanding the data
- Identify duplicate data
- Identify quantitative data
• Distribution (frequency) of values
• Minimum and maximum
- Identify categorical data
• Number of categories
• Design a possible solution (database portion only)
o Identify all data issues with the supplied dataset
o Decide on tables, and columns
- Derived columns (Virtual column option)
o Identify required integrity constraints
- Primary key constraint
- Unique constraints
- Foreign key constraints
• Options
o ON DELETE CASCADE
o ON DELETE SET NULL
- RECURSIVE RELATIONSHIPS (self-referencing relationship)
o Identify data constraints
- Data type
- DEFAULT
- NOT NULL
- CHECK
• Valid range
• Maximum length
- UNIQUE
- ON DELETE CASCADE
- Column and table constraints
o Identify database triggers
• Explain your analysis and database requirements
o "Defend" your solution and provide details on the business value of your solution
o Write for the target audience which, in this Exercise is someone who has basic computer knowledge
Lab: Business Situation
You are responsible for the creation of a database for use in a future study of customer purchases and demographics.
Lab Details
Analyze and document your analysis in a report - use the objective section of this document as your guide. Include at a minimum:
1. Necessary tables and fields
2. Business rules and relationships
3. Data issues and how you intend to address them in your database design
4. Primary and foreign keys
5. CHECK, UNIQUE and NOT NULL constraints
6. Questions you would need to ask to obtain additional information about the dataset
7. Any assumptions you're making about the dataset
8. Any risks in converting the dataset to a relational database
9. Benefits of analyzing the dataset using a relational database versus leaving it as an Excel spreadsheet
Deliverables
You will submit a report of your analysis and database requirements. The report describes your analysis of a dataset, and the design of a database. It includes documentation of your research, understanding, questions and decisions about this design. Be thorough, clear and organized about your report. Include and cite references in APA format if you used any.
o The report includes
- A description of your analysis of the dataset and the database solution
• Includes your consideration of each of the bulleted items listed in the objective section of this document
• References and citations
o The report is professionally-formatted
- APA style
o Organize your report to take the reader from the start of your analysis through to the solution.
Attachment:- Work-Sheet.rar