Data Modeling and Normalization Rules

This assignment discusses various aspects of database management, including data modeling approaches, key concepts, user-defined types, features, and normalization rules. It also covers hierarchical structures, root types, and validation procedures. The assignment explains the concept of 'Chasm Trap' and 'Fan Trap', which are common pitfalls in database design. Additionally, it provides an overview of the 1NF (First Normal Form), 2NF (Second Normal Form), and 3NF (Third Normal Form) rules for ensuring data consistency and integrity.

Project-7

Table of Contents
1. Discuss about the functions and features.................................................................................3
2. Describe:...................................................................................................................................4
3. Explain “Chasm Trap”.............................................................................................................5
4. Explain a “Fan Trap”................................................................................................................6
5. Discuss about the 1NF, 2NF and 3NF. ....................................................................................6

1. Discuss about the functions and features
Data modeling approach:
Data modeling approach is a process of documenting a complex system software design
and understand their overall functionality by using the diagram. It has to be represent in the best
way to understand the flow of information. For Example- Entity relationship diagram is the best
example to shows the relationships between the entities and their attributes in the database
system. A data modeler uses this model to view the same data and ensure that all the entities and
relationship, process and information flow must be identified.
Keys:
Key help to identify the any row of tables, and allows establishing relational between one
or more database tables.
Primary key -
 It is a unique identifier.
 It does not contain the same value.
Composite key-
 It has multiple attributes to the unique identify rows in DBMS table.
 Composite is any part of compound key can foreign.
Primary Index-
 It provides the predictable and fast access to maintain the functionality of system. It is also defined as an ordered in data files which may generate form the filed where
candidate key and unique value in every records.
Time stamp:
Time stamp is an important for keep maintain the records of information being
exchanged or created. Public key infrastructure is applied the concept on time stamps when TSA
combine with the hash and other information includes the authoritative time, At results generated
digitally with timstamp private key.

Types:
User defined type:
A user define type is derived the existing data type that can help to use the UDT for
extend the built in type which already available. There are different type of user defined type
such as
Reference type
It is scalar type that commonly share the representation with one of built in data types. It
also shared for all types in hierarchy manner.
Structure type-
A structure type is based on the user defined data type that has formed a structure in the database
system. It can use the tables, columns, views etc.
features:
 High performance Indirect and direct useable
User defined functions
User defined function is provided by the user of program and environment, in the context
where they usually assumption is that when the functions are built into the program and
environment. In relational database management system, A user defined functions provide the
mechanism for extending the functionality of database server. SQL standard is mainly
distinguish between the table and scalar functions.
For Example- Function with argument return no values.
function without argument.
Data types and their function.
 Character – this data type is accepts the strings which including Unicode.
 Boolean – it supports the storage of different values in the form of true and false. There is
no parameter require for declaring.
 Integer- It consists of numeric values which store between 2^ -31 and 2^31.

2. Describe:
Database identifiers and their impact on usability
Database identifier is a based on the object that can serve everything in the SQL server. It
is also type of database object that help to create table, review, procedures, indexes, triggers,
constraints etc. It is identifier depends on the level at instances objects such as DBMS name,
logins etc.
 It has ability to control and manage the overall database system in proper manner. In this
way, user can use the SQL server as an identifier for managing the task. It is positively impact to increase the overall processing.
Normalization rules or processes
Normalization rules are the important part of data management to arrange all the complex
information in form of table by using normal forms.
Normalization rules:
 Eliminating the repeating groups and also make separate table for every set regarding the
attributes.
 Isolate the independent multiple relationships where no table may contain two or more
normal relationship. Eliminate the columns and not dependent on the key where attributes does not contribute
on the description of key.
Types of hierarchies
Hierarchies are the model where data organized into the different structure with each
record having one parent records and many other children.
 Super type is an entity type that has created a relationship with one or more sub types. It
also contains attributes that common sub types.
 Sub-types are the groups of super types entity that contain unique attributes but it will be
different from each sub type. For Example- credit card, bank account, people and
insurance.

Root types related to the development of structure data types
A structure data type is basically form of user defined data type that contain a sequences
of attributes. Each contain a unique attributes that help to describe the instances of type. The
structure types exhibit a behavior called as inheritance. This type can have sub types other
structure that reuse all the attributes and contain specific attributes. In this way, Super-type is a
hierarchy of root-types structure.
Validation procedures and processes
Database validation process is that when the data enter in the DBMS that checked to
make sure it is correct. It is carried out by the system when it enters the input data that will check
by the system to validate the values. There are different ways to validate the data and
information such as
 Type
 Presence
 Unique identifier
 Range check
 Restricted choice
 Format
3. Explain “Chasm Trap”
Chasm trap is basically occurs when two many to one join create on single table. For
example- customer can place many orders and loan.
For Example-
Illustration 1

4. Explain a “Fan Trap”
Fan trap occurs when it develops one to many joins link a table which is also turned to
another by another one to many.
For Example-
This diagram is representing the Fan trap when it has created one to many joins in the tables.
5. Discuss about the 1NF, 2NF and 3NF.
1 NF (First Normal Form)
 Each and very table contain a single value.
 Each record need to be unique.
For Example-
It is representing the 1 Normal Form.
Illustration 2
Illustration 3

2NF (First Normal Form)
In 2nd normal form, if the candidates key contains more than one attributes and at any part of key
should not determine in proper manner.
For Example-
Functional dependencies:
Roll number → student name
cornum → corname
This table is splits into 2 NF forms. This form is based on the full functional dependencies.
Illustration 4
Illustration 5

3NF (Third Norma Form)
This normal form has no transitive dependency. There is not prime attributes derive somethings.
For Example-
Illustration 6
Illustration 7

Illustration 8
Illustration 9

Project 2 has created 3 NF relational because it has no functional dependencies during the
design of database tables.
Illustration 10