University Database Design and Implementation Report with SQL

This report details the process of database design and implementation, starting with an Entity Relationship (ER) diagram to model the relationships between entities such as teams, players, coaches, and parents within a soccer league context. The report then delves into database normalization, explaining the first, second, and third normal forms to eliminate data redundancy and anomalies. The final section focuses on implementing a database using MS SQL Server, including the execution of four SQL queries to retrieve and manipulate data. The report concludes by summarizing the key aspects of database design, emphasizing the importance of normalization and the practical application of SQL for database management and query execution, providing a comprehensive overview of the database design and implementation process.

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the Student
Name of the University
Author’s note:

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
Task A – ER Diagram:....................................................................................................................2
Task B – Normalization:..................................................................................................................4
Task C – SQL:.................................................................................................................................6
Bibliography:...................................................................................................................................8

2DATABASE DESIGN AND IMPLEMENTATION
Introduction:
Database is the organized set of data which can be accessed electronically from devices.
Database supports storing, retrieving, modifying and deleting data. In order to perform those
operations on database, system needs a database management system. In simple words, the
purpose of database management system is tracking and controlling operations on database.
Database design the process of determining stricture of a database (Arulraj 2017). Normalization
is an important factor in database design. This approach decomposes database tables so that data
redundancy and data anomalies can be prevented. Mainly three normalization forms are
considered to be standard such as first, second and third normal form. Microsoft SQL Server is a
relational database management system developed and maintained by Microsoft. SQL server is
also called T-SQL (Hashim 2018).
This report has three parts such as database modelling, normalization and database
implementing. The first part will produce an entity relationship model. In second part a database
of grade report will be decomposed up to third normal form. In the final section, a database will
be implemented in MS SQL Server and four queries will be run on the database.

3DATABASE DESIGN AND IMPLEMENTATION
Task A – ER Diagram:
Figure 1: Entity Relationship Diagram
(Source: Created by Author)
The database model has been implemented using chain’s database notation approach.
Above entity relationship diagram has four entities. Each of the entities has names in upper case.

4DATABASE DESIGN AND IMPLEMENTATION
The model shows relation name between each entities. As per the model, one coach can coach
one team but team may not have any coach at a time. A team can have maximum of one coach.
A single team can have more than one player but a single player can register into at most one
team. One player can have only one parent but each parent can have at least one or more than
one player. Each entity has a primary key. This implies that every table is strong entity. The
required attributes within the tables refers to foreign keys. As players have many to one relation
with team and parent entities, the foreign keys of team and parent will be used as foreign keys in
player table. Street, state, city and postal code together form address of the parent. Each human
in the entity such as player, parent and coach has common fields like firstName and lastName.
Task B – Normalization:
If a table is in first normal form then it should maintain these four rules. Every single
attribute within the database should be single valued. Values recoded within columns must be of
single domain. Combination of every column must result into a distinctive row. It must not be an
issue in which order data is recorded. Given database has fifteen attributes. Each of these
attributes are single valued. The combination of values in all the attributes result into unique
column because studentID, InstructorID and coursID are always unique values. The order of
storing data into database does not matter.
If a table is in second normal form, it must maintain these two guidelines. The database
should be in first normal form. The attributes within the database should not have any partial
dependency. Partial dependency can be seen in a database if a non-key (primary) attribute is
reliant on another non-key attribute. Given database is already in first normal form that has been
discussed on above section. coursID and instructor are non-key attributes within this database.

5DATABASE DESIGN AND IMPLEMENTATION
The approach of removing partial dependency is creating separate table in database with all the
dependency attributes and introduce foreign keys. In order to remove partial dependency from
this database course and instructor tables are created. A reference to primary key of these two
table has been used in StudentCourse table. Then the new database looks like following.
Student(StudentID, StudentName, StudentMajor, StudentStreetAddress, CourseID,
Grade)
Course(CourseID, CourseTitle, CourseCreditPoints, CourseSemesterYear,
CourseInstructorID)
Instructor(InstructorID, InstructorName, InstructorOffice)
If a table is in third normal form then it should maintain these two guidelines. The
database should follow guidelines of second normal form as well as it must not contain transitive
dependency. Transitive dependency is considered to be an secondary association among values
within the same table that results into functional dependency. Transitive dependency happens
between three or more attributes. In the given database, Grade is dependent on StudentID
through CourseID. In order to resolve this transitive dependency, a new table called
StudentCourse is created where studentID, courseID and grade will be placed. This table will
have studentID and courseID as composite primary key.
Student(StudentID, StudentName, StudentMajor, StudentStreetAddress, CourseID,
Grade)
StudentCourse(StudentID, CourseID, Grade)

6DATABASE DESIGN AND IMPLEMENTATION
Course(CourseID, CourseTitle, CourseCreditPoints, CourseSemesterYear,
CourseInstructorID)
Instructor(InstructorID, InstructorName, InstructorOffice)
Task C – SQL:
Query 1: Select Parts.PartsID from PARTS where description like '%plastic%';
Query 2: select SUPPLIERS.sname from SUPPLIERS
inner join CATALOGUE on SUPPLIERS.SupplierID = CATALOGUE.SupplierID
inner join PARTS on CATALOGUE.PartsID = PARTS.PartsID
where PARTS.colour like '%red%'
AND CATALOGUE.cost < 2000;
Query 3: select PARTS.pname, CATALOGUE.cost from PARTS
inner join CATALOGUE on PARTS.PartsID = CATALOGUE.PartsID
where CATALOGUE.cost <= (select MIN(c.cost) from CATALOGUE c);
Query 4: select SUPPLIERS.SupplierID from SUPPLIERS
inner join CATALOGUE on SUPPLIERS.SupplierID = CATALOGUE.SupplierID
inner join PARTS on CATALOGUE.PartsID = PARTS.PartsID
where PARTS.colour like '%red%'
or SUPPLIERS.state = 'NSW';

7DATABASE DESIGN AND IMPLEMENTATION

8DATABASE DESIGN AND IMPLEMENTATION
Conclusion:
From the above study it can be concluded that database design and implementation is a
cautious process. The database must be designed such way that it can answer business queries.
The entity relationship model developed in first section is following chain’s database notation.
Normalization is a very crucial process in database design. Data anomalies can create huge
issues in database. The process of normalization has been properly shown in the report. All the
explanation required to understand normalization process has been provided. In the
normalization section, final database design has been proposed. Database in MS SQL Server has
been implemented using traditional T-SQL approach. SQL Server management has been used to
implement database in SQL Server. The report provided all the required information to share
details of works done within it. The information are shared with accuracy.

9DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Albarak, M., Alrazgan, M. and Bahsoon, R., 2017. Identifying and Managing Technical Debt in
Database Normalization Using Machine Learning and Trade-off Analysis. arXiv preprint
arXiv:1711.06109.
Amin, M., Romney, G.W., Dey, P. and Sinha, B., 2019. Teaching Relational Database
Normalization in an Innovative Way. The Journal of Computing Sciences in Colleges, p.48.
Amran, N., Mohamed, H. and Bahry, F.D.S., 2018. Developing Human Resource Training
Management (HRTM) Conceptual Model Using Entity Relationship Diagram
(ERD). INTERNATIONAL JOURNAL OF ACADEMIC RESEARCH IN BUSINESS AND
SOCIAL SCIENCES, 8(12).
da Paixão, R.S.S. and Pereira, C.P., 2018. Web Application for Model, Share and Conversion of
Entity-relationship Diagrams for Person Visually Impaired. In CSEDU (1) (pp. 405-411).
Dunn, C. and Nel, L.D., 2017. Entity Relationship Diagram Mapping.
Guagliardo, P. and Libkin, L., 2017. A formal semantics of SQL queries, its validation, and
applications. Proceedings of the VLDB Endowment, 11(1), pp.27-39.
Mukherjee, S., 2019. Indexes in Microsoft SQL Server. arXiv preprint arXiv:1903.08334.
Mukherjee, S., 2019. SQL Server Development Best Practices. International Journal of
Innovative Research in Computer and Communication Engineering, 10.
Noh, H.N., Bahari, M. and Zakaria, N.H., 2018. A Conceptual Model of Database Normalization
Courseware Using Gamification Approach. In PROCEEDINGS OF NEW ACADEMIA
LEARNING INNOVATION (NALI) SYMPOSIUM 2018 (p. 23).

10DATABASE DESIGN AND IMPLEMENTATION
Wang, Y., Liu, J., He, X. and Wang, B., 2018. Design and realization of rock salt gas storage
database management system based on SQL Server. Petroleum, 4(4), pp.466-472.
Yang, L. and Cao, L., 2016. The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern Education and
Computer Science, 8(7), p.1.