Answer & Explanation:Please Read the attachment for this project Create the original (i.e. in provided format) and 3NF dependency diagrams as well as an ERD in Visio that captures the information requirements described below.Combine your diagrams and ERD into one file and save in PDF format, and submit your file on Blackboard by midnight on Saturday, October 1. Make sure your name is on your document. File name must follow this convention: YourLastName_Normalization_Fall2016.pdf (for example, Veltri_Normalization_Fall2016.pdf)Any questions regarding the assignment should be directed to your professor and not your classmates. This is an individual assignment and should be completed individually!To document dependencies you can use the following notation:StudentID –> StLname, StFname, StDOBClassID –> ClassRoom, ClassCapacity, CourseID, InstructorIDInstructorID –> InstructorLname, InstructorFnameCourseID –> CourseName, CourseHrs, CourseDescriptionClassID, StudentID –> GradePlease see the Attached file I need it in Visio File pleasefor question 2 that will help you to understand it Thats what I will be graded for So it is just Diagram and ERD Diagram in Visio and 1 Paper about BUS rule
20160929181011chapter_8_normalization_and_other_data_modeling_methods__1_.pptx
20160928174000normalization_assignment_fall_2016__2_.docx
Unformatted Attachment Preview
Normalization and Other
Data Modeling Methods
Chapter 8
Normalization
An alternative database design tool to
data modeling
A theoretical foundation for the
relational model
Application of a series of rules that
gradually improve the design
Functional dependency
A relationship between attributes in an entity
One or more attributes determine the value of another
attribute
An identifier functionally determines all the
attributes of an entity
stock code → firm name, stock price, stock quantity, stock
dividend
If we know stock code we know the value of firm name, etc.
Multi-valued dependency
Formula (i.e. several attributes together determine others)
(stock dividend, stock price) → yield
Full functional dependency
Yield is fully functionally dependent on
stock dividend and stock price because
both of these attributes are required to
determine the value of yield
(stock dividend, stock price) → yield
Determinant
An attribute that fully functionally
determines another attribute
• e.g., stock code determines stock PE
Multidetermination
A given value can determine multiple values
A multidetermines B
A→ B
e.g., Department multidetermines course
Multivalued dependency means functional
dependencies are multivalued
Attribute relationships
One-to-one
A value of an attribute determines the value
of another attribute and vice versa
A → B and B → A
e.g.,
• CH → Switzerland
• Switzerland → CH
Attribute relationships
One-to-many
A value of one attribute determines the
value of another attribute but not vice
versa
• country name → currency unit
• currency unit not → country name
Attribute relationships
Many-to-many
Neither attribute determines the other
A not → B
B not → A
• country name not → language
• language not → country name
⬧ French and Flemish is spoken in Belgium
⬧ French is spoken in many countries
Functional Dependence
Concepts
Concept
Definition
Functional dependence
The attribute B is fully functionally
dependent on the attribute A if each
value of A determines one and only
one value of B.
Functional dependence
(Generalized definition)
Attribute A determines attribute B if
all of the rows in the table that agree
in value for attribute A also agree in
value for attribute B.
Fully functional
dependence
(composite key)
If attribute B is functionally
dependent on a composite key A but
not on any
Subset of that composite key, the
attribute B is fully functionally
dependent on A.
Types of Functional
Dependencies
Partial dependency: Functional dependence in
which the determinant is only part of the primary key
Assumption – One candidate key
Straight forward
Easy to identify
Transitive dependency: An attribute functionally
depends on another nonkey attribute
Normal forms
A classification of relations
Evaluating and correcting table structures to
minimize data redundancies
Reduces data anomalies
Assigns attributes to tables based on
determination
Normal forms
First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)
Normalization Process
Objective is to ensure that each table
conforms to the concept of well-formed
relations
Each table represents a single subject
No data item will be unnecessarily stored in
more than one table
All nonprime attributes in a table are
dependent on the primary key
Each table is void of insertion, update, and
deletion anomalies
Normalization Process
Ensures that all tables are in at least 3NF
Higher forms are not likely to be encountered in
business environment
Works one relation at a time
Starts by:
Identifying the dependencies of a relation (table)
Progressively breaking the relation into new set of
relations
Normal Forms
First normal form (1NF)
All rows must have the same number of
columns
Single valued attributes only
Conversion to First Normal
Form
Repeating group: Group of multiple entries of same
type can exist for any single key attribute occurrence
Existence proves the presence of data redundancies
Enable reducing data redundancies
Steps
Eliminate the repeating groups
Identify the primary key
Identify all dependencies
Conversion to First Normal
Form
Dependency diagram: Depicts all
dependencies found within given table
structure
Helps to get an overview of all relationships
among table’s attributes
Makes it less likely that an important dependency
will be overlooked
Conversion to First Normal
Form
1NF describes tabular format in which:
All key attributes are defined
There are no repeating groups in the table
All attributes are dependent on the primary key
All relational tables satisfy 1NF requirements
Some tables contain partial dependencies
Subject to data redundancies and various
anomalies
First Normal Form (1NF) Dependency Diagram
Second normal form (2NF)
Violated when a nonkey column is a fact about part of
the primary key
A column is not fully functionally dependent on the
primary key
customer-credit in this case
order
itemno
customerid
quantity
customer-credit
12
57
25
OK
34
679
3
POOR
Conversion to Second Normal
Form
Steps
Make new tables to eliminate partial dependencies
Reassign corresponding dependent attributes
Table is in 2NF when it:
Is in 1NF
Includes no partial dependencies
Second Normal Form (2NF)
Conversion Results
Third normal form (3NF)
Violated when a nonkey column is a fact
about another nonkey column
A column is not fully functionally
dependent on the primary key
exchange rate in this case
stock
stock code
nation
exchange rate
MG
USA
0.67
IR
AUS
0.46
Conversion to Third Normal
Form
Steps
Make new tables to eliminate transitive
dependencies
• Determinant: Any attribute whose value determines
other values within a row
Reassign corresponding dependent attributes
Table is in 3NF when it:
Is in 2NF
Contains no transitive dependencies
Third Normal Form (3NF)
Conversion Results
Data modeling and
normalization
Data modeling is often an easier path to
good database design
A high-fidelity data model will be of
high normal form
5NF is likely to create the most
problems
Check for special rules
Data modeling methods
A widely known model is Chen’s entityrelationship (E-R) approach
There is no standard for the E-R method
Nearly all data modeling approaches are very
similar because they share common concepts
Learning is readily transferable between
methods
Goal
Learn to think like a data modeler
Different dialects and greater precision
(e.g., cardinality) come easily once the
basics are mastered
Key points
Normalization is one approach to data
modeling
The are multiple representations for
data model
Learning to model is difficult
Learning to represent a model is easy
Data and Information Management
Normalization Assignment
Instructions:
Create the original and 3NF dependency diagrams as well as an ERD in Visio that captures
the information requirements described below.
Combine your diagrams and ERD into one file and save in PDF format, and submit your file on
Blackboard by midnight on Saturday, October 1. Make sure your name is on your document.
File name must follow this convention: YourLastName_Normalization_Fall2016.pdf (for
example, Veltri_Normalization_Fall2016.pdf)
Any questions regarding the assignment should be directed to your professor and not your
classmates. This is an individual assignment and should be completed individually!
Scenario:
A friend of yours works for a company that runs charter flights and has asked you to help her evaluate
a database that contains the table structure shown below in Table 1. She created this table so she can
keep track of the charter flights flown by the company’s planes. AC_NUMBER is the aircraft number.
CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is based on roundtrip miles, including pickup points. (Hint: Look at the data values to determine the nature of the
relationships. For example, note that employee Melton has flown two charter trips as pilot and one trip
as copilot.)
Table 1. Sample CHARTER Records
Attribute Name
CHAR_TRIP
CHAR_DATE
CHAR_CITY
CHAR_MILES
CUST_NUM
CUST_LNAME
CHAR_PAX
CHAR_CARGO
PILOT
COPILOT
FLT_ENGINEER
LOAD_MASTER
AC_NUMBER
MODEL_CODE
MODEL_SEATS
MODEL_CHG_MILE
Sample
Value
10232
15-Jan-2014
STL
580
784
Brown
5
235 lbs.
Melton
1234Q
PA31-350
10
$2.79
Sample
Value
10233
15-Jan-2014
MIA
1,290
231
Hanson
12
18,940 lbs.
Chen
Henderson
O’Shaski
Benkasi
3456Y
CV-580
38
$23.36
Sample
Value
10234
16-Jan-2014
TYS
524
544
Bryana
2
348 lbs.
Henderson
Melton
Sample
Value
10235
17-Jan-2014
ATL
768
784
Brown
5
155 lbs.
Melton
1234Q
PA31-350
10
$2.79
2256W
PA31-350
10
$2.79
Assignment
1. You recognize that the existing table structure is very poor and want to help your friend.
Given what you see in the table above, draw the dependency diagram for the existing table
structure. Label all transitive and/or partial dependencies. Use the format shown in class
(class solution posted under Class Practice tab). (25 points)
2. You notice several dependencies in the original diagram and now need to break up the
dependency diagram you drew to produce dependency diagrams that are in 3NF (3 Normal
Form). As you work through the diagram you might have to create a few new attributes
based on the scenario description. You also should make sure that the new dependency
diagrams contain attributes that meet proper design criteria; that is, make sure that there
are no multivalued attributes, that the naming conventions are met, and so on.) (25 points)
3. Now that you have a better understanding of the data that your friend is trying to track, list
the business rules that you have identified (for example, every charter trip must have a at
least one crew member, i.e. pilot, but could have many). (10 points)
4. You know that normalization is a very useful tool to help eliminate data redundancies,
however, it is incapable of serving as the sole source of good database design. Every good
database design must start with a data model. Thus, to help your friend you decide to create
an ER diagram to properly document and design the database that she needs. Based on the
tables that you identified through normalization, create a Crow’s Foot ERD. Label all
entities, attributes, keys (primary and foreign) and relationships. Make sure all attributes
are atomic and properly named. Resolve all many-to-many relationships by creating
associative entities and identify their primary and foreign keys. Prepare your ERD in Visio.
(40 points)
…
Purchase answer to see full
attachment
Order a plagiarism free paper now. We do not use AI. Use the code SAVE15 to get a 15% Discount
Looking for help with your ASSIGNMENT? Our paper writing service can help you achieve higher grades and meet your deadlines.
Why order from us
We offer plagiarism-free content
We don’t use AI
Confidentiality is guaranteed
We guarantee A+ quality
We offer unlimited revisions