I’m working on a databases question and need guidance to help me study.

I don’t know how to handle this Databases question and need guidance.

Instructions

Read Connolly/Begg text:
Chapter 19 – Methodology – Monitoring and Tuning the Operational System section 19.2
Chapter 20 – Security and Administration
Review MS Office 365 Access 2016 text:

Assignment
HINT: Review the Normalization Process: Parts and Suppliers One-to-Many Example in the Terms and Concepts Discussion.

Term Project – week 8 – (17%):
You were just hired to create a database to track blood draws at a lab.
Discussions with the representatives decided on two entities, Blood Draws and Patients; the following key points were agreed:
1) Each blood draw is assigned a unique DrawNum
2) Each Patient has a unique PatID
3) Each blood draw is taken from one patient, each patient may have more than one blood draw.
4) No fields beyond those in the report / spreadsheet are needed

DrawNum is a unique number assigned to each blood draw, Date is the date of the blood draw, Nvials is the number vials drawn, PatID is the patient ID – it is unique for each patient, fName is the patient’s first name, lName is the patient’s last name, DOB is the patient’s date of birth

The objective of this exercise is to demonstrate an understanding of basic concepts covered in the course. The exercise is a straightforward application of those concepts – there are no “hidden” complexities – should you identify something in the key points or data that adds complexity, contact the instructor before submission – you may be over thinking the exercise.
The sample data may not represent all possible values – consider each field’s domain during the assessment and design.
The goal is to replace the following report / spreadsheet with a relational database. The submission will consist of a word compatible document to record the design process, and an Access DB.

(A matching Excel spreadsheet is attached. This is provided to reduce typos – do not assume the spreadsheet is a table in the final design.)

Here is the un-normalized (UNF) relational schema (table) notation for the above report:
BloodDraws (DrawNum, Date, Nvials, PatID, fName, lName, DOB)

The functional dependencies are:
DrawNum – – > Date, Nvials, PatID, fName, lName, DOB
PatID – – > fName, lName, DOB

The specific tasks to perform are listed below, the percentage corresponds to the grade weight for each task: Organize your document to match the tasks.
Name your document Last Name_TermProject (i.e. Smith_TermProject). When you are asked to provide an explanation or description, you mustinclude sufficient content to demonstrate that you understand the definition, term, concept, etc. and how it applies to this exercise.

SUGGESTION: Review the Terms and Concepts Forum, especially the Normalization One-To-Many example. There is also a normalization MP4 file that can be downloaded from the Resources section.

Follow the below outline in your submission – include Task numbers and questions with your answers.
1) Review the existing report, functional dependencies, and sample data (consider field domains and common knowledge) then document any assumptions you feel are appropriate (beyond those in the key points) and identify the initial entities. (10%)

2 Explain functional dependency and describe each row of the functional dependencies provided above (use field names and values). (15%)
(you do not need to describe full, partial or transitive dependency in this task)

3) Based on multiplicity – in plain English explain the relationship between the Entities provided in the above description – (either one-to-many, or many-to-many). (15%)

4) Design: all tables and fields must be specified at each normal form level
a) First Normal Forum (1NF) assessment / action 10%
– Copy the 1NF definition from the text (include quotes and page number)
– Assess the UNF table provided and if necessary, make the changes needed to conform to the 1NF definition. Explain any action taken and why (use field names and values).
– Specify all 1NF table(s) using relational schema notation or spreadsheet format (see – the above example or page 111 Figure 4.2.6 of the text). Ensure primary keys are identified.

b) Second Normal Form (2NF) assessment / action 10%
– Copy the 2NF definition from the text (include quotes and page number)
– Assess the 1NF table(s) in the previous section and if necessary, make the changes needed to conform to the 2NF definition. Explain any action taken and why (use field names and values).
– Specify all 2NF table(s) using relational schema notation or spreadsheet format. Ensure primary keys are identified.

c) Third Normal Form (3NF) assessment / action 10%
– Copy the 3NF definition from the text (include quotes and page number)
– Assess the 2NF table(s) in the previous section and if necessary, make the changes needed to conform to the 3NF definition. Explain any action taken and why (use field names and values).
– Specify all 3NF table(s) using relational schema notation or spreadsheet format. Ensure primary keys are identified.

5) Using the 3NF tables in your design, create a new MS Access database, load the sample data provided, and ensure any table relationships are established.
Name your database Last Name_TermProject (i.e. Smith_TermProject). (5%).
6) Create a Query, for the following request: List each Patient and their blood draws; include the following fields (in the order provided) PatID, fName, lName, DrawNum, date, and Nvials, sort Ascending by lName. Name the Query BloodDraws. (5%)
7) Create a Form: to enter a New Patient, only enter Patient related fields – do not include fields associated with a blood draw in the Form, name the form NewPatient. (5%)
8) Create a Report: List all Blood Draws (5%)
Upload the database to the assignment area as one of the deliverables
9) Submission content organization, clarity, spelling and grammar (10%)

Requirements: instructions above