Database Normalization and Implementation for Desklib

This article discusses the normalization and implementation of a database for Desklib, including the entities, attributes, data types, primary and foreign keys, and security issues related to the database. It also includes a dependency diagram, relational schema, and entity relationship diagram. The article cites various sources for further reading on the topic.

Database 1
Part 1: -
1. Dependency diagram: -
2. Decompose the relation into a set of 3NF relations: -
 First Normal Form (1NF): -

Database 2
 Second Normal Form (2NF): -

Database 3
 Third Normal Form (3NF): -

Database 4
3. Relational Schema: -

Database 5
4. Entity Relationship Diagram (ERD): -

Database 6
Part 2: -
1. Implementation: -
 Introduction: -
 According to the case study each player is expected to attend 20 training sessions. So
here a player is attending one or many training session. Each training session is
attended by six or more players. So we can say that a training session is attended by
one or many players. According to this scenario, there are many to many relationships
between players and training session. So we will need to add more tables to
normalize the database so that the database can be created without duplicity.
 We know that players need to register for the training session before attending that.
So here a player can register in one or many training sessions and a training session is
being registered by one or many players. There is many to many relationships
between players and training session. So here I created a registration table between
players and training session table to remove many to many relationships between
them and establish the database in full normalization.
 Now a player has one or many registrations and each registration is related to one and
only one player. A training session has one or many registrations and each
registration is related to one and only one training session.
 According to the case study, registered players attend the training session. Here zero
to one relationship between registrations and attend training. So that a registration
has zero or one attend training and each attends training is related to one and only one
registration.

Database 7
 Player table store information related to player ID, player Name, DOB, Player
position. This table only stores the attributes that corresponds to a Player.
 Training session table store information related to training session ID, date, time and
Venue. This table only stores the attributes that corresponds to Training.
 Registration table store information related to Registration Number, Player ID, and
Training Session ID and Registration date. This table only stores the attributes that
corresponds to Registration.
 Attend training table store information related to registration Number, the note of the
player and rating of the performance that each player gives at the end of training
session.
 Business Rules: -
 A player registers many training session.
 A player has one or many registrations and each registration is related to one
and only one player.
 A training session has one or many registrations and each registration is
related to one and only one training session.
 A registration has zero or one attend training and each attends training is related to
one and only one registration.
 Entitles: -
1. layerP
2. raining sessionT
3. Registration
4. Attend training_

Database 8
 Entities, attributes, data type, size, Primary and foreign key: -
2. Security issue: -
 Irregularities in database: - you need to test website and assure that your database is
protected. If any corruption is found then fixed it. The administrator or developer knows
everything those threads occur in the database. Otherwise, anyone fetches the database
and it creates the security issue.
 Limitless administration access=poor data protection: - it is ensured that the duties of
administrator and limited access to the database by the user. The administrator has the
duty to add the protection on user access. The user only accesses limited data. Otherwise,
the user accesses or fetches the sensitive data from the database and it creates the issue in
the database security.
 Stolen database backups: - there are two types of threats: one is internal and other is
external. The company always worries about internal threats as compared to external
Entity name Attribute name Data type size
Player
Player_ID (PK) VARCHAR 10
Name Varchar 100
DOB Date -
Playing_Position Varchar 50
Training_Session
Training_Session_ID (PK) Varchar 10
Date date -
Time time -
venue varchar 100
Registration
Registration_ID varchar 10
Training_Session_ID(FK) varchar 10
Player_ID (FK) varchar 10
Date date -
Attend_Training
Registration_ID (PK, FK)) varchar 10
Note varchar 200
Rating integer 10

Database 9
threats. Because they not sure of their employee's loyalty. Anybody threat data and sell to
the third party for more profit. This type of problem creates the security issue.
Bibliography: -
Atzeni, P., & De Antonellis, V. (1993). Relational database theory. Redwood City, Calif.:
Benjamin/Cummings.
Awad, E., & Gotterer, M. (1992). Database management. Danvers, Mass.: Boyd & Fraser Pub. Co.
Carter, J. (1995). The relational database. London: Chapman and Hall.
Date, C. (2007). An introduction to database systems. Boston: Pearson Education.
Pathak, N. (2008). Database management system. Mumbai [India]: Himalaya Pub. House.
Powell, G. (2006). Beginning database design. Indianapolis, Ind.: Wiley.
Teorey, T. (2011). Database modeling and design. Amsterdam: Elsevier.