Category: Databases

Please read the instructions carefully, In question “e” : Replace 4NF by 3NF.
So the question will be :
e) Fill the table below with all schemas after normalization up to the 3NF. + the screenshots must be from replit program. and there must be a ( PowerPoint ) presentation provided with the project files also provide me the codes in case.
There is and ( project solved ) file.
It’s an example of the answers and the diagrams but it must be not the same
Please avoid plagiarism.

Be sure to study Chapter 9 to do this exercise. You will be creating a database schema which means there will be tables to represent the date in TopText Publishing. What other concepts do you have to consider in creating the DBMS?
Use Microsoft Access to create a DBMS for the imaginary company called TopText Publishing. Add several sample records to each table.
TopText Publishing is a textbook publishing company with a headquarters location, a warehouse, and three sales offices that each has a sales manager and sales reps. TopText sells to schools, colleges, and individual customers. Many authors write more than one book for TopText, and more than one author writes some books. TopText maintains an active list of more than 100 books, each identified by a universal code called an ISBN.
NOET :
I need the answer to be in word file , the first page ( databases schema with relations) , the other pages contain tables with sample data no more than 7 record for each table.

Select one dataset from the datasets provided in the bellow link.
For 28 Data Analysis Projects to Boost Your Skills [2023 Guide]:
https://www.springboard.com/blog/data-analytics/data-analysis-projects/
For more free public datasets for EDA:
https://www.tableau.com/learn/articles/free-public-data-sets
After the dataset is selected (or assigned), analyze the data using Microsoft Excel to discover the structure of data, trends, patterns, or any anomalies in the data based on your own hypothesis.
Perform the following six tasks.
You should use visualization to aid your answers.
Your project will include two main parts:
The final project report which must incorporate all the following 6 tasks and written using the provided template. (10 marks distributed among the below tasks).
A presentation that illustrates your 6 tasks completed in the project. (4 marks)
==========================================================
Task 1: Understand and describe the nature and structure of the selected dataset. (2 marks)
Describe the dataset. Your description should answer the following questions: is it reliable? how was it collected? What its size?
Identify the features of dataset.
Propose hypothesis / assumptions (between 2 numerical variables) to validate.
Task 2: Check if your selected features have any of the following issues. Describe how you conducted the tests and how you addressed the issues. Support your answers with screenshots of the issues before and after the fixes. (1 mark)
Missing values (0.25 for the test, fix and screenshot)
Duplicate values (0.25)
Data outliers (0.25)
Any noise or irregularities (0.25)
Task 3: Provide descriptive statistics for the selected features using statistical method to understand the dataset more and answer the following analysis questions: (2 marks)
Include any of the measure of central tendency such as the mean, median, and mode.
Describe the spread of your data. This may include the measure of variance, standard deviation, skewness, and kurtosis.
(You are encouraged to impose other analysis questions based on any trend you notice in the dataset).
Task 4: Validate the hypothesis in Task 1 by investigating the relationship between two quantitative variables you have chosen using correlation, regression and R-squared with possible conclusions. (2 marks)
Task 5: Show visual representation of your analysis (hint: use the right chart/graph for your data analysis). (1 mark)
Task 6: Build an active Dashboard which summarizes the most crucial factors (variables) that will help in decision-making process, and then demonstrate the effectiveness of your selection of those factors in the decision-making process. (2 marks)
Project Report

Please follow attachment instruction
Answer in same file
3 questions
Project Report
Each Project Report will be evaluated according to the marking criteria mentioned in each question section
Presentation
The presentation schedule with date and allocated timing will be shared with the students via Blackboard before the end of Week # 13.

a) Identify the entity types, attributes, keys. (2 marks)
b) Identify the relationship and cardinalities. (2 marks)
c) Draw the ERD. (2 marks)
d) Schemas before Normalization. (1.5 marks)
e) Schemas after Normalization. (1.5 marks)
f) Create the tables. (1.5 marks)
g) Populate your tables with at least 5 rows. (1.5 marks)
h) Execute the requested sample queries.

Variable Creation Assignment
Instructions:
The assignment deliverable is to submit your completed Excel Workbook with the Variable Feature Creation Analysis tasks completed as a SINGLE EXCEL FILE.
Use the provided “Assignment Submission Form – Variable Feature Creation” document to organize your output and answers.
Continue working on the same “Housing Market Dataset – Post Midterm” workbook from the “Relationship Testing” Lab and HW. We will continue to build on this workbook for all Lab, Homework, and Final Project objectives. If you are not using the Post Midterm file, please begin now. You can copy your Relationship Significance Testing sheets into the Post Midterm workbook.
Save your work as you go and ultimately make sure you save it in your cloud storage environment (Microsoft OneDrive or Sharepoint, or GoogleDrive). My computer crashed is not a valid excuse for late or incomplete work.
Check off each of the tasks as you complete them. It is expected that the student has attended/or watched and attempted the lecture tutorials in order to understand how to complete the assignment tasks. Save and rename your Excel workbook as “LastName Firstname – Variable Feature Creation Lab and Homework.”
Submit your Excel workbook AND your assignment submission form to the “Assignments” folder in D2L (Desire 2 Learn).
**Points will be deducted for failure to follow all instructions, regardless of how trivial you think they are.
Please ensure that:
You are only submitting one Excel workbook with all tasks completed with the instructed worksheet tabs.
You have named your submission according to the instruction above.
Your worksheets are organized and easy to follow.
Task 1: Quantitative Binning (Refer to the Variable Creation Lecture PPT, Manual, Workbooks and Videos for help)
Instructions: Create a new worksheet called “Quant Binning” and copy the Housing ID, MasVnrArea, TotalBsmtSize, Lot Area, and Sale Price Variables into the worksheet. Perform all analysis tasks for Task 1 in this worksheet.
Often times, when our quantitative variables do not have a very strong relationship with the target, we can create a new variable to look at the information in a new way. We can also use binning and discretizing techniques to prepare data from classification models (not taught in this course), and to smooth the influence of skewness and outliers. One option we have is creating a categorical variable by binning the values of a quantitative variable into groups or categories. After we create the variable, we need to analyze its relationship with the target and if there is a relationship, we need to either rank code it or dummy code it depending on the relationship we see.
**We will only cover integer (whole) number encoding in this course, but there are more advanced ways we can assign weights and influence to the discrete categories we create.
CREATE: Let’s bin the values for TotalBsmtSize(Sqft), LotArea(Sqft), and MasVnrArea(Sqft). I must see your “If” statements for credit, do not copy and paste the data as values after creating or you will receive no credit.
Create a new ordinal categorical variable called “Basement Size” by binning the values for “TotalBsmtSize” according to the definitions below. Copy and paste your nested IF statement formula into the Assignment Submission form.
0 sqft= “None”
1-500 sqft = “Small”
501 – 1000 sqft = “Medium”
1001 – 1500 sqft = “Large”
1501+ sqft = “Extra Large”
Create a new ordinal categorical variable called “Lot Size” by binning the values for “LotArea” according to the definitions below. Copy and paste your nested IF statement formula into the Assignment Submission form.
0 – 4999 = “Extra Small”
5000 – 9999 = “Small”
10000 – 14999 = “Medium”
15000 – 19999 = “Large”
20,000+ sqft = “Extra Large”
Create a new ordinal categorical variable called “Masonry Veneer Discrete” by binning the values for “MasVnrArea” according to the definitions below. Notice how they are coded as discrete numbers. Copy and paste your nested IF statement formula into the Assignment Submission form.
0 sqft= 0
1-200 sqft = 1
201 – 400 sqft = 2
401 – 600 sqft = 3
601+ = 4
ASSESS: Look at the bivariate statistics for each creation. Does there appear to be a relationship between Basement Size and Sale Price, and between Lot Size and Sale Price? Compare means, standard deviations, and category sizes. Are there any categories with low frequencies that we should be concerned about being underrepresented?
Create a pivot table (using the Pivot Table tool in the “Insert” tab in Excel). Select your newly created “Basement Size” variable and the original “Sale Price” variable for your “Table/Range” and place the table in the Existing Worksheet.Add “Basement Size” to the Rows box.
Add “Sale Price” to the Values box and change the value field settings to calculate “Average of Sale Price”, instead of “Sum of Sale Price”
Add “Sale Price” to the Values box and change the value field settings to calculate “StdDev of Sale Price”, instead of “Sum of Sale Price”
Add “Sale Price” to the Values box and change the value field settings to calculate “Count of Sale Price”, instead of “Sum of Sale Price”
Make sure your categories are in order and change “Row Labels” to “Basement Size”.
Copy and paste your pivot table as a picture into the Assignment Submission form and assess whether you feel a relationship may be present and why.
Excel Workbook Reference:
Create a pivot table (using the Pivot Table tool in the “Insert” tab is Excel). Select your newly created “Lot Size” variable and the original “Sale Price” variable for your “Table/Range” and place the table in the Existing Worksheet.Add “Lot Size” to the Rows box.
Add “Sale Price” to the Values and change value field settings the calculated “Average of Sale Price”, instead of “Sum of Sale Price”.
Add “Sale Price” to the Values box again and change value field settings the calculated “StdDev of Sale Price”, instead of “Sum of Sale Price”.
Add “Sale Price” to the Values box again and change value field settings the calculated “Count of Sale Price”, instead of “Sum of Sale Price”.
Make sure your categories are in order and change “Row Labels” to “Lot Size”.
Copy and paste your pivot table as a picture into the Assignment Submission form and assess whether you feel a relationship may be present and why.
Using the correlation function in Excel (=CORREL(variable 1 range, variable 2 range)), calculate a correlation value for the new discretized version of masonry veneer area and sale price. Compare it with the correlation value in the table below. Did discretizing the variable improve the correlation?
Task 2: Quantitative Aggregations (Refer to the Variable Creation Lecture PPT, Manual, Workbooks and Videos for help)
Instructions: Create a new worksheet called “Quant Create Agg” and copy the Housing ID, Outdoor Living Variable (WoodDeck, OpenPorch, EnclosedPorch, 3SsnPor, ScreenPorch, and PoolArea), the year variables (YearRemod and YrSold),.and Sale Price Variable into the worksheet. Perform all analysis tasks for Task 2 in this worksheet.
CREATE: Let try some mathematical aggregations of variables to see if we can create new variables that have a stronger relationship with the target than each variable individually. I must see your formulas in Excel for credit, do not copy and paste the data as values after creating or you will receive no credit.
Create a new variable called “Total Outdoor Living Area” by adding (=sum()) together all the outdoor living area variables (WoodDeck, OpenPorch, EnclosedPorch, 3SsnPor, ScreenPorch, and PoolArea)
Create a new variable called “Average Outdoor Living Area” by averaging (=average()) all the outdoor living area variables (WoodDeck, OpenPorch, EnclosedPorch, 3SsnPor, ScreenPorch, and PoolArea)
Create a new variable called “Year Since Remodel” by subtracting the YearRemod from YrSold variable.
ASSESS: Now we have to determine the best version of the variables. We can only use each variable 1 time, so we can either use the original variable as it was given, or an aggregated/created formulation.
Create a correlation matrix with all the original variables, all the created variables, and the target variable. Copy and paste the correlation matrix as a picture in your Assignment Submission form.
Excel Workbook Reference:
Provide is brief write up of which version is the best version and why.

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.
InstructionsLearning Outcome(s):
CLO4: Develop a standard database using DBMS.
CLO3: Analyze algorithms for query processing.
CLO1: Recognize database file organization and indexing.
Suppose you are a database administrator in a large company, and you have been asked to create a new database. Create your database scenario and follow the following instructions.
You should use the same project document to prepare your answer. A word file and a PDF file should be provided.
Each group can have a minimum of 3 and a maximum of 4 students.
You must use MySQL for database creation and manipulation.
Create ONE database scenario. All your answers should be according to the selected scenario.
Support your answer with a detailed explanation and screenshots.
14 Marks
Learning Outcome(s):
CLO4: Develop a standard database using DBMS.
CLO3: Analyze algorithms for query processing.
CLO1: Recognize database file organization and indexing.
Project Description
Part 1: Database Description and Design
1-Describe your database scenario (Don’t select any database which we studied during lab session or lecture) with at least 5 Entities. (1 Mark)
2-Design an Entity Relationship (ER) diagram to capture the scenario requirements. Make sure cardinalities, relationships, and primary keys are clear. State any assumptions that may affect the ER diagram. Your ER should include at least 4 entities and 1 “Many-to-many” relationship.
(2 Marks)
3-Convert the ER diagram to Class diagram. (1 Mark)
Part 2: Create and Populate Relations
1-Map the ER diagram to a relational model and create the relations in MySQL. (1 Mark)
2-Enter at least 20 tuples (records) in each relation/table. Your solution should include the screenshots of your relations with data. (1 Mark)
Part 3: Indexes and Queries
1-Write any retrieval query that includes a selection condition and show how MySQL internally performed this query. (1 Mark)
2-Create an index for the same column used in the previous selection condition. (1 Mark)
3-Repeat the same retrieval query in step (1) and show how MySQL internally performed the query this time. Discuss how the created index improves the performance of your database. (1 Mark)
4-Write any retrieval query that includes at least one join condition and one selection condition and show the result of this query. (1 Mark)
5-Show the Query Statistics and Execution Plan for the above query. (1 Mark)
Part 4: Roles and Privileges
1-Create two roles and assign the following privileges to the roles (1 Mark):
-Give all privileges to the first role.
-Give only insert privileges to the second role who can further assign the same privileges to others.
2-Create two accounts and assign the above roles to the created accounts (each account with a different role). (1 Mark)
3-Verify the privileges assigned to each account. (1 Mark)

i uploaded the rubric and you can see it and do based on what it wants only do Phase 2 and i uploaded phase 1 you can see it and uploaded a sample
please use simple english not advanced

Need to create a project for Database Management Systems direction as per attached file which contains project details and requirements.

I chose to repeat my experience with you because I know I survived with you.
I would like to choose the technique that I will use for research
Then show it to a doctor and get approval
Then send me an outline of your research
Then I measure the extent of his comfort with the doctor
Then you go to work