DATA:

Subject Class Section Class# Course Title Days Meeting Times Location Session Status$*$

EMS $20641$A $44085$ EMS $20641$A Pathophysiology for the Health Professions F $08$:$00$ AM $-07$:$00$ PM CHESTER $1/11/2021-2/12/2021$ Open

EMS $20642$A $47225$ EMS $20642$A Pathophysiology for the Health Professions MW $08$:$30$ AM $-02$:$00$ PM CHESTER $1/11/2021-2/12/2021$ Closed

BSK $140$A $17124$ BSK $140$A Whole Numbers MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $140$A $16779$ MTE $140$A Operations with Positive Fractions MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $240$A $17062$ MTE $240$A Operations with Positive Decimals and Percents MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $340$A $16846$ MTE $340$A Algebra Basics MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $440$A $16888$ MTE $440$A First Degree Equations and Inequalities in One Variable MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $540$A $16924$ MTE $540$A "Linear Equations, Inequalities and Systems of Linear Equations in Two Variables" MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $640$A $16960$ MTE $640$A "Exponents, Factoring and Polynomial Equations" MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $740$A $17097$ MTE $740$A Rational Expressions and Equations MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $840$A $17096$ MTE $840$A Rational Exponents and Radicals MW $11$:$00$ AM $-12$:$20$ PM CHESTER $1/11/2021-2/15/2021$ Closed

MTE $940$A $17041$ M

$*endof$ data$*$

You are a consultant in the role of the database analyst.

To help, they provided this data dictionary for the table CLASS$\_$SCHEDULE in the format FIELDNAME:DATATYPE:DESCRIPTION

This is a list of field names, data type, and descriptions of field contents

FIELDNAME DATATYPE DESCRIPTION

Subject CHAR$(3)$ DEPARTMENT

Class INTEGER CLASS NUMBER $($NOT SECTION OR SEQUENCE SERIAL$)$

Section VARCHAR$(12)$ SECTION NUMBER

Class# INTEGER CLASS SERIAL NUMBER $($UNIQUE IDENTIFIER$)$

Course VARCHAR$(25)$ COMBINED FILED SUBJECT$-$CLASS$-$SECTION

Title CHAR$(99)$ CLEAR TEXT TITLE OF CLASS

Days CHAR$(11)$ DAY$($S$)$ OF WEEK OFFERED

MeetingTimes DATETIME START AND END TIME ON DAY OF CLASS

Location CHAR$(25)$ CAMPUS NAME

Session CHAR$(25)$ FIRST AND LAST DAY OFCLASS

Status$*$ CHAR$(8)$ OPEN$=$ACCEPTING STUDENTS, CLOSED$=$NOT OPEN

You must answer several questions such as what subject has the most classes and what subject has the most ZOOM classes. OH$!$ And the Registrar is working on a new advertising campaign demonstrating how we are enabling remote learning via synchronous $($lecture based$)$ ZOOM classes and asynchronous $($guided learning without specified class sessions$)$ online classes.

TASK $1$: Answer the $4$ questions $($answer type at end of each question$)$

Your first task is to analyze the available data structure and make suggestions to better organize the data for ease of analysis. This is a first step in data normalization. You should look at the sample data and make the following decisions:

Would you split any of the data fields into two or more independent field and which ones? Why? $($short discussion$)$

Would you discard any data field and which ones? Why? $($short discussion$)$

If CLASS# is a unique key, is it appropriate for the data set? $($YES$/$NO$)$

When analyzing the data, which fields would make a good GROUP BY for aggregate functions? $($list$)$

TASK $2$: Describe a query to search for null entries

At the end of this analysis, you present at least two qualitative data screening requirements. One is looking for null or empty fields and one is looking for invalid data entries against control lists.

Example for Invalid Data:

To validate the data: Create a query that compares the foreign key fields of each record in the sample data to a master control table of unique key entries for that field.

Department lists and their authorized SUBJECT value $($unique key SUBJECT$).$

SECTION identifiers that correspond to specific date and time entries $($unique key SECTION$).$

Authorized LOCATION values $($unique key LOCATION$)$ for the campuses.

Side note: To prevent erroneous data, create a trigger on data entry that executes on attempted INSERT to the database that compares the key values and returns a message to the user.

Describe the query you would use to check for NULL values in A SINGLE particular field. You choose the field and describe the query.