Database Normalization, Design, and SQL Homework Assignment

This document presents a solution to a database homework assignment focusing on database normalization and SQL queries. The assignment involves normalizing database tables, specifically the 'Client' and 'Consultant' tables, and demonstrating the differences in table structures based on different relationship mappings. It includes creating a database named 'BITS2', designing the 'Client' and 'Consultant' tables, populating them with data, and executing an SQL query to retrieve client names along with their corresponding consultant's full name. The document provides table structures, sample data, and the SQL query used to join the two tables, offering a practical example of database design and querying. Desklib is a platform where students can find similar solved assignments and study tools.

Running head: DATABASE AND NORMALIZATION
Database and normalization
Name of the Student:
Name of the University:
Author Note

1
DATABASE AND NORMALIZATION
Part 1
Answer to question number 4
The tables in the database after performing the normalization has been provided
below:
Client (ClientNum (pk), ClientName, Street, City, State, ZipCode, Balance, CreditLimit,
ConsltNum(fk))
Conslutant (ConsltNum(pk), LastName, FirstName, Street,City, State, ZipCode, Hours, Rate)
Answer to question number 5
Conslutant (ConsltNum(pk), LastName, FirstName, Street,City, State, ZipCode, Hours, Rate,
ClientNum (fk))
Client (ClientNum (pk), ClientName, Street, City, State, ZipCode, Balance, CreditLimit)
Answer to question number 6
There are differences in between the tables created in question 4 and question 5. As
the solution resulting in question 4 depicts that there are two different tables, the clients and
the consultant where the consultants are referenced to the clients table. Hence a consultant
can handle a number of clients however not the other way round. In addition to this, the
solution to question number 4 provides the same tables however in this solution the clients
are references to the consultants and hence, a client can have a number of consultants but not
the other way round.
Part 2
The database with name BITS2 is created.

2
DATABASE AND NORMALIZATION
Part 3
Client Table
Consultant Table

3
DATABASE AND NORMALIZATION
Part 4
Client Data
Consultant Data

4
DATABASE AND NORMALIZATION
Part 5
Select clients.clientName, CONCAT (consultant.LastName, " " ,consultant.FirstName) As
'conultant'
From clients
Inner Join consultant On consultant.consultNum = clients.ConsltNum;

5
DATABASE AND NORMALIZATION
Bibliography
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).
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.