Databases store, organize, collect, and retrieve data, and, …
Databases store, organize, collect, and retrieve data, and, given the amount of data some databases store, how they’re 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 creating a simple query to retrieve data, normalize a sample database, and design an entity relationship diagram (ERD).
For further instruction, read the attach document.
Assiognment in Data base Fundamental
About this Assignment
Databases store, organize, collect, and retrieve data, and, given the amount of data some databases store, how they’re 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 creating a simple query to retrieve data, normalize a sample database, and design an entity relationship diagram (ERD).
Part 1 – Retrieve Data From a Database
Consider the following sample database.
Figure 1: ERD for Library Database
Given the following sample data, you will see that certain tables are linked via primary and foreign keys (e.g., Client and Borrower: ClientId is on both tables).
Write the following SQL statements to retrieve data:
· Select all borrowers
· Select all books borrowed by borrowers, order by borrow date
· Select all books and include the author first and last name
· Insert a new client with an occupation of pilot
Note that your queries will not return data, unless you would like to create and set up a functioning database. The purpose of this exercise is to ensure you are able to write fully-formed and appropriate SQL statements.
Part Two – Database Normalization
Write a brief 500 to 750 word essay that evaluates the miniature database below. Currently, the database is not in third normal form (3NF). Your analysis should identify the dependencies that exist in the current database setup and explain the steps needed to transform it to third normal form.
Miniature Database
Following is the table structure for the tables in the current database.
Table Structure for Produce
COLUMN_NAME
DATA_TYPE
NULLABLE
ITEMID
CHAR (5)
No
SUPPLIERID
CHAR (10)
No
PLUCODE
CHAR (4,2)
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
Part Three – Develop an ERD – Entity Relationship Diagram
Now that you have identified the steps necessary to bring this database to third normal form (3NF), you will create a diagram that displays the new layout and the relationships between the tables. It should depict how you successfully removed dependencies in the tables to create a relational database model.
Formatting & Sources
Save your final assignment in Microsoft Word, pasting in the SQL query that you had created and your analysis of how the sample database can be normalized to 3NF. You can create the ERD using Word drawing tools, or a free charting tool such as Lucidchart. Be sure to take a screen capture of the final diagram and paste into the Word document.
For the essay portion, please use APA format. You may refer to the course material for supporting evidence. You may also use other sources as needed, but please make sure you cite them using APA format. If you use any Study.com lessons as sources, please also cite them in APA (including the lesson title and instructor’s name). If you are unsure about how to use APA format for your paper and sources, please see the following lessons:
· What is APA Format? Style & Definition
· How To Format APA Citations
Related Lessons
The following lessons will be helpful as you complete your assignment:
· Basic SQL Query Syntax
· First Normal Form in DBMS with Examples
· Third Normal Form in DBMS with Examples
· Designing a Relational Database: Tutorial & Overview
· Relationships in SQL: Examples & Explanation
Grading Rubric
Your assignment 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
SQL Query Creation (x3)
One or fewer queries are provided; queries do not follow SQL syntax or are wrong
Two or fewer queries provided; does not follow SQL syntax
Queries are brute force, unnecessarily long, or not formatted in a readable manner
Queries are succinct, well-formatted, and would produce the desired results
30
Normalization Analysis (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; APA formatting is used correctly.
30
Entity Relationship Diagram (x3)
Diagram missing or does not address the sample data
Diagram partially complete; does not address normalization or move database toward 3NF; hard to read
Diagram complete, with most components complete; closer to 2NF
Diagram shows a 3NF database with all connections; notated properly and easy to follow
30
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
Before You Submit
When you complete your assignment, we suggest taking some time to check for any errors or to add any finishing touches. We also suggest that you use online plagiarism checkers, such as PlagScan, to make sure that your assignment is not too similar to any existing materials. Plagiarized submissions will NOT be graded.
image1.png
essaysresearch.org Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we\’ll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Why Hire essaysresearch.org writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
