Databases store, organize, collect, and retrieve data, and, given the amount of data some databases store, how they are built can seem disjointed and confusing. To overcome these obstacles and make databases useful, computer scientists developed complex algorithms and database designs to create efficient and accessible databases. Relational databases are an example of this type of solution. They store data in tables that are related by different unique keys so that relevant data is stored together, but different data combinations can still be retrieved using the unique identifying keys.
In this assignment, you will explore these topics by evaluating a miniature database and identifying its core components.
Database Normalization
Write a brief 400 to 500-word essay that evaluates the miniature database below. Your essay should identify which version of database/SQL it uses, explain the database's level of normalization, and the choice of keys and dependencies.
Miniature Database
Table Structure for Produce
COLUMN_NAME DATA_TYPE NULLABLE
ITEMID CHAR (5) No
SUPPLIERID CHAR (10) No
PLUCODE CHAR (NUMBER) No
PRODUCENAME CHAR (15) No
TYPE CHAR (10) No
STOCKQTY NUMBER (4,2) No
NXTDELIVERY DATE No
Table Structure for Animal Products:
COLUMN_NAME DATA_TYPE NULLABLE
ITEMID CHAR (5) No
SUPPLIERID CHAR (10) No
ANPRDNAME CHAR (15) No
TYPE CHAR (10) No
STOCKQTY NUMBER(4,2) No
NXTDELIVERY DATE Yes
Table Structure for Grains:
COLUMN_NAME DATA_TYPE NULLABLE
ITEMID CHAR (5) No
SUPPLIERID CHAR (10) No
GRAINNAME CHAR (15) No
TYPE CHAR (10) No
STOCKQTY NUMBER(4,2) No
NXTDELIVERY DATE Yes
Table Structure of Suppliers:
COLUMN_NAME DATA_TYPE NULLABLE
SUPPLIERID CHAR (10) No
LASTDELIVERY DATE Yes
SPECIALTY CHAR (15) Yes
ACTIVE CHAR (1) No
Table Structure of Purchases:
COLUMN_NAME DATA_TYPE NULLABLE
ITEMID CHAR (5) No
TOTALBOUGHT NUMBER (8,2) Yes
TOTALSOLD NUMBER(8,2) Yes
TOTALREV NUMBER(10,2) Yes
MARGIN NUMBER(10,2) Yes
Grading Rubric
Your essay will be graded based on the following rubric:
Category Unacceptable (0-2) Needs Improvement (3-6) Good (7-8) Excellent (9-10) Total Possible Points
Clarity & Structure (x1) Does not provide adequate explanation of what will be discussed. Is difficult to follow or lacks reasoning Attempts to explain context are present but unclear or in need of further detail. Produces a good essay with clear reasoning and adequate scope and complexity. Essay is easy to follow, grammatically correct and clearly justified. 10
Normalization Explanation (x3) Analysis is lacking or incomplete. Explanation is attempted but with unclear reasoning or context. Explanation is clearly stated and correct. Normalization is thoroughly explained, thoughtfully structured and factually accurate. 30
Database/SQL Explanation (x2) Database is not clearly explained. An attempt to explain database/SQL was made but reasoning was flawed or lacking. Database explanation is clear, and sound. Strongly written explanation with well-reasoned and comprehensive context. 20
Dependency Planning (x4) Dependencies are not clear and plan is not built out. Dependencies are acknowledged but plan is flawed or lacking context. Dependencies are well thought out, explained, and solved. Clear thorough analysis of dependencies is fleshed out and detailed, taking many factors into account. 40