Category: Databases

To access the case, you need to purchase it for a small fee using the following website. Add the receipt of your purchase to the end of your report.Foxcore Retail (A): Designing a Database: https://hbsp.harvard.edu/import/1105169
Plagiarism is not acceptable and will lead to Zero marks.
Due date: 19/10/2023
Marks: 5%Read the case carefully and Draw ER diagram using Barker’s notation. Include all entities, attributes, unique identifiers, relationships, relationship types and cardinalities.
Resolve any M-M relationship, if any.
Include supertype/subtypes, if any.

Pick any dataset relevant to your major that you would like to analyze. Avoid use the same or similar datasets to one you use for your final project. Make sure that the dataset has continuous and categorical variables.
Use R, Python or other tool to calculate descriptive statistics part of the dataset at large.
Use one of the categorical variables to divide dataset into meaningful parts. Calculate descriptive statistics for each of the groups.
Submit a Word report describing each step and a result of this process, include relevant scripts and outputs produced by the tool you use. Your report has to include:Mean, Standard deviation, median, max and min values.
Box plots showing comparison of the groups.
Histogram for at least one variable and the description of the variable distribution
Textual description for a – b, explaining what they tell about the data.
Discussion on the possible predictive analytics questions applicable to the selected dataset.

To access the case, you need to purchase it for a small fee using the following website. Add the receipt of your purchase to the end of your report.Foxcore Retail (A): Designing a Database: https://hbsp.harvard.edu/import/1105169
Plagiarism is not acceptable and will lead to Zero marks.
Due date: 19/10/2023
Marks: 5%Read the case carefully and Draw ER diagram using Barker’s notation. Include all entities, attributes, unique identifiers, relationships, relationship types and cardinalities.
Resolve any M-M relationship, if any.
Include supertype/subtypes, if any.

Include the below numbering scheme in the submission. DO NOT include the Questions or other content from the instructions.
Part 1)
Identify 4 database constraints (examples can include: Not NULL, primary key, unique, domain; there can be different ones as well) and data integrity, security, and availability issues related to relational databases.
Part 2)
HINT: Review the Normalization Process: Parts and Suppliers One-to-Many Example in the Terms and Concepts discussion.
BACKGROUND
We want to keep track of the price we charge for each type of part, the supplier for each type of part, and the amount we pay the supplier for each type of part (the cost). Each supplier can provide us with many different types of parts, but each part can be provided to us by only one supplier. This requires a one-to-many relationship.
FUNCTIONAL ANALYSIS:
Part —- > Price, Cost, Supplier, Street, City, State, Zip, Telephone
Supplier —- > Street, City, State, Zip, Telephone
Part —- > Price, Cost, Supplier
Table:
Parts (Part, Price, Cost, Supplier, Street, City, State, Zip, Telephone)
COMPETE THE FOLLOWING:
1. Include a definition of 2 normal form
2. Is the above table in 2NF?
3. Using the table and field names in the above table explain your answer.

check the instructions carefully
i will also share a sample on how the final answer should look like
no AI OR CHAT GPT

Full question is attached in a file
please read all instructions before accepting the bid
you have to use SQL and and make a video of what you are doing and narrate verbally as well.
Requirements: As required
ATTACHMENTS

Full question is attached in a file
please read all instructions before accepting the bid
you have to use SQL and and make a video of what you are doing and narrate verbally as well.

PLEASE MAKE SURE TO FOLLOW ALL THE INSTRUCTIONS AND NOT USE ANY RESOURCES OUTSIDE OF THE PROVIDED FILES. DOING SO WILL RESULT IN 0. ALSO, PLEASE COMPLETE ALL THE STEPS PROVIDED.
Use only the database file sent to you via messages/email. Failure to do so will result in a zero and you will be referred to the Deans office.
Student Handout
Your organization is developing and testing a database. You are asked to fix any errors in the test database—provided to you by your instructor- and improve upon it. (Note : Achieve 2nd Normal form. Ignore dependency of State and City on ZIP code). Delete any old macros in the tables that might be interfering with the creation of tables..
*. The database has no relationships defined. Primary keys may be missing or not correct.
*. Please note that all data in the database is test data and hence can be modified/replaced as needed. If a column does not exist, create it.
*. Each item belongs to a Category.
1. Enforce entity integrity for all tables (Hint: Primary Key) (Exceptions: HISTORY tables).
(0.50 pts)
2. Enforce referential integrity for all tables (Hint: Foreign Key) (Exceptions: HISTORY tables).
(0.50 pts)
3. The ORDER table in the database is not normalized and may have to be broken into two tables- ORDER and ORDERDETAILS.
(0.40 pts)
4. Each table must be in a (correct) relationship with at least one other table. (Exceptions: HISTORY tables). ( 0.10 for each relationship to a max of 1 pt)
(0.40 pts
5. Each item can be supplied by many suppliers, and each supplier can supply many items. Therefore, new tables may have to be created (Hint: Bridge tables—also known as associative entity table or junction table).
(0.20 pts)
6. Each employee can be in one or many projects. Each project can be associated with one or many employees. Therefore, new tables may have to be created (Hint: Bridge tables—also known as associative entity table or junction table).
(0.20 pts)
8. All possible values in the State column for CUSTOMERS, EMPLOYEES, and SUPPLIERS will come from the STATES table.
(0.60 pts total – 0.20 points each)
9. To further improve the database, enforce column-level integrity through field properties, input mask, validation rule, and lookup. Enforce the following rules:
a. FName and LName of EMPLOYEES and CUSTOMERS tables are required (Hint: Field properties).
(0.10 pts)
b. HomePhone and Mobile Phone of EMPLOYEES and CUSTOMERS number should be in the phone number format: (Hint: input mask).
(0.10 pts)
c. Employees can only be from the USA, Canada, or Mexico: (Hint: Validation rules).
(0.10 pts)
d. The State column in the EMPLOYEES, CUSTOMERS, and SUPPLIERS tables should contain a drop-down menu showing all states available. (Hint: lookup tab).
( Total .30 pts – 0.10 pts each)
10. Saving past addresses: When employees, customers, and suppliers change addresses, your organization wants to store their previous addresses. For this purpose, whenever there is a change in these tables, the changes need to be transferred into a history table. Use figure 1 below as reference. These history tables don’t have to be part of any relationships.
( Total 0.60 pts – 0.20 pts for each )
Close the database completely before uploading.

Overview
You have a job interview. They have given you the task of writing a summary that explains how you would write a report for one of their clients. They also want you to give a brief explanation justifying the approach you took.
Directions
The potential employer would like you to use a data set called FleetMaintenanceRecords to complete an analysis of data and summarize your findings using clear language for a nontechnical audience. They’ve asked you to find themes that can be used to help them manage their fleet of trucks. Create a summary of the analysis you conducted that you would use to communicate the findings to stakeholders. Place the deliverables for your instructor and the potential employer in the Analysis and Summary Template.
Analyze the data you’ve been provided to identify themes. Gather the following guiding information:
Review part-replacement frequencies and types and create a hypothesis that can be used by the fleet management team to better deal with vehicle maintenance. You’ll need to create a table called Parts Maintenance and load the data into it. Put this table in the database named after yourself. Load this data set from the ‘/home/codio/workspace’ path and run queries to find the results.
Which parts are being replaced most?
Is there a region of the country that experiences more part failures and replacements than others?
Can you identify a region (Northeast, Southeast, Midwest, Northwest, Southwest) that has more reasons for replacement? Use the Region Definitions sheet to identify states in each region.
How might the fleet maintenance team use the information to update its maintenance schedule? Your response should be supported by the data you’ve found.
Which parts are being replaced most due to corrosion or rust?
Which parts are being replaced because of mechanical failure, like a flat tire or rock through the windshield?
Write a brief summary of your analysis that takes the information from Step 1 and presents it in a way that nontechnical stakeholders can understand.
Outline the approach that you took to conduct the analysis.
What queries did you use to identify trends or themes in the data?
What are the benefits of using these queries to retrieve the information in a way that allows you to provide valuable information to your stakeholders?
Lastly, explain how the functions in the analysis tool (MySQL) allowed you to organize the data and retrieve records quickly.
What to Submit
Submit your responses using the Analysis and Summary Template. Each screenshot and its explanation should be sized to approximately one quarter of the page, with a description written below the screenshot. After you download the template, rename your copy by adding your last name to its file name. This will help organize your work once it has been submitted.
Supporting Materials
The following resource supports your work on this assignment:
Document: Region Definitions
Use the definitions of United States regions in this document to assist with this activity.
Module Five Activity Rubric
CriteriaProficient (100%)Needs Improvement (75%)Not Evident (0%)Value
Analyze Data to Identify ThemesAnalyzes the data provided to identify themesShows progress toward proficiency, but with errors or omissions; areas for improvement may include identifying all themes that the questions ask forDoes not attempt criterion30
Write Summary of AnalysisWrites a brief summary of the analysis that presents it in a way that nontechnical stakeholders will be able to understandShows progress toward proficiency, but with errors or omissions; areas for improvement may include communicating all parts of the analysis clearlyDoes not attempt criterion30
Outline the ApproachOutlines the approach taken to conduct the analysisShows progress toward proficiency, but with errors or omissions; areas for improvement may include producing a stronger explanation of the how and why behind what was doneDoes not attempt criterion30
Explain Functions of Analysis ToolExplains how functions in the analysis tool (MySQL) were used to organize data and retrieve records quicklyShows progress toward proficiency, but with errors or omissions; areas for improvement may include describing standard ways that this tool could have been used based on its capabilitiesDoes not attempt criterion10
Total:100%

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.
3 Marks
Learning Outcome (3):
Create Entity-Relationship model, Relational model, and write SQL queries.

Question One
Consider a hypothetical online management system for the Saudi Electronic University that uses a relational database to manage its operations. This system needs to keep track of information about students (including student ID, name, major, and email), courses (including course ID, name, instructor, and department), and enrollments (including enrollment ID, student ID, course ID, grade, and semester).
Describe how you would design the relational model for this system. What tables would you create? What attributes would each table have? How would you use primary and foreign keys to represent relationships between the tables?

Answer:
2 Marks
Learning Outcome (3):
Create Entity-Relationship model, Relational model, and write SQL queries.
Question Two
Consider a database system for SEU that stores information about students, courses, faculty, and course enrollments. A student has a unique ID, name, major, and email. Each course has a unique course ID, name, department, and credits. Faculty members have unique faculty IDs, names, departments, and specializations. An enrollment associates a student, a course, a faculty member (as the instructor), and a semester. One student can enroll in multiple courses, but each enrollment is for only one course. A faculty member can instruct many courses, and each course can have multiple students enrolled over different semesters.
Based on this description, create the ER diagram.
Answer:
Learning Outcome (3):
Create Entity-Relationship model, Relational model, and write SQL queries.
2 Marks
Question Three
Define the Entity Integrity Constraint and Referential Integrity Constraint then explain their differences in a relational database. Explain why the DELETE operation may violate only the referential integrity and not the entity constraint. Provide examples to illustrate each constraint using the database system for SEU from question 1.
Answer:
1 Mark
Question Four
Learning Outcome(s): CLO1
Explain the concepts and architectures involved in the database development.
Using your own words, explain two advantages of the Three Tier Client-Server Architecture over the Single Tier Centralized DBMS Architecture.Answer: