Database Design and Development for SmartProjects Company - BN204

This report provides a comprehensive database design and development solution for SmartProjects Company, addressing entity identification, attribute definition, relationship mapping, functional dependencies, and normalization. The solution includes an entity-relationship diagram illustrating the database structure and detailed database modeling in MS SQL Server, showcasing the creation of tables for Employee, Customer, Department, Cust_Project, Project, and Manager. The normalization process ensures data integrity and efficiency by adhering to 1st, 2nd, and 3rd normal forms, eliminating redundancy and dependency issues. This document is designed to help students understand the practical application of database design principles and can be found with other helpful resources on Desklib.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database Design and Development
Name of the Student
Name of the University

1DATABASE DESIGN AND DEVELOPMENT
Table of Contents
1. Database Development:...............................................................................................................2
2. Entity Relationship Diagram:......................................................................................................5
3. Database Modeling in MS SQL Server:......................................................................................6
Bibliography:...................................................................................................................................8

2DATABASE DESIGN AND DEVELOPMENT
1. Database Development:
1.a. Entity Identification: The entities that has been identified by understanding the user
requirements are employee, customer, project and department.
1.b. Adding Attributes: The attributes of the employee table are as below.
Employee (empID, first_name, last_name, date_of_birth, address, contactNo, username,
password)
The attributes of the customer table are as below.
Customer (customer_number, first_name, last_name, address, PNum, project_title,
payment)
The attributes of the project table are as below.
Project (PNum, project_title, strat_date, managerID, managername, managerContactNo)
The attributes of the manager table are as below.
Department (departmentID, name)
1.c. Relationships among Entities: One customer can be associated with many projects
but one at a time – many to many
One employee can work in a single project but one project will have many employees –
one to many
One department can have many employees but an employee can work only in one
department – many to one.

3DATABASE DESIGN AND DEVELOPMENT
1.d. Functional Dependencies:
empID → (first_name, last_name, date_of_birth, address, contactNo, username, password)
customer_number → (first_name, last_name, address, PNum, project_title, payment)
customer_number, PNum → (project_title, payment)
PNum → (project_title, strat_date, managerID, managername, managerContactNo)
managerID → (managername, managerContactNo)
departmentID → (name)
1.e Normalization:
1st normal form describes that the attributes in a table has to be atomic and each table
must have a unique identifier. As per the user requirement, described in the case study of
SmartProjects Company, the attributes are atomic and each table has a unique identifier.
The 2nd normal form describes that the non-key attributes have to be depended on the
primary key attribute only. Therefore, the tables must not have any partial dependency among
attributes. The functional dependencies among the attributes has been shown below.
empID → (first_name, last_name, date_of_birth, address, contactNo, username, password)
customer_number → (first_name, last_name, address, PNum, project_title, payment)
customer_number, PNum → (project_title, payment)
PNum → (project_title, strat_date, managerID, managername, managerContactNo)
managerID → (managername, managerContactNo)

4DATABASE DESIGN AND DEVELOPMENT
departmentID → (name)
Therefore, the new tables will be.
Employee (empID, PNum, departmentID, first_name, last_name, address, contactNo,
username, password)
Department (departmentID, name)
Customer (customer_number, first_name, last_name, address)
Cust_Project (customer_number, PNum, payment)
Project (PNum, project_title, strat_date, managerID)
Manager (managerID, name, ContactNo)
The 3rd normal form describes that no table should have transitive dependency among
the attributes. Currently there are no transitive dependency among the attributes. Therefore, the
tables are in third normal form.

5DATABASE DESIGN AND DEVELOPMENT
2. Entity Relationship Diagram:
Figure 1: The Entity Relationship Diagram of 1. SmartProjects Company Database
(Source: Created by Author)

6DATABASE DESIGN AND DEVELOPMENT
3. Database Modeling in MS SQL Server:
Employee Table:
Customer Table:
Department Table:
Cust_Project Table:
Project Table:
Manager Table:

7DATABASE DESIGN AND DEVELOPMENT

8DATABASE DESIGN AND DEVELOPMENT
Bibliography:
[1] Gouhar, A., 2017. Database Management System. International Journal of Engineering
Science, 11766.
[2] Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible
highly available and scalable database management system. FernUniversität, Fakultät für
Mathematik und Informatik.
[3] Yunus, M.A.M., Krishnan, S.K.G., Nawi, N.M. and Surin, E.S.M., 2017. Study on
Database Management System Security Issues. JOIV: International Journal on
Informatics Visualization, 1(4-2), pp.192-194.
[4] Reddy, T.B., Thomas, A.D., Stamatis, D., Bertsch, J., Isbandi, M., Jansson, J.,
Mallajosyula, J., Pagani, I., Lobos, E.A. and Kyrpides, N.C., 2014. The Genomes OnLine
Database (GOLD) v. 5: a metadata management system based on a four level (meta)
genome project classification. Nucleic acids research, 43(D1), pp.D1099-D1106.
[5] Hababeh, I., Khalil, I. and Khreishah, A., 2015. Designing high performance web-based
computing services to promote telemedicine database management system. IEEE
transactions on services computing, 8(1), pp.47-64.
[6] Prout, A., Kepner, J., Michaleas, P., Arcand, W., Bestor, D., Bergeron, B., Byun, C.,
Edwards, L., Gadepally, V., Hubbell, M. and Mullen, J., 2015, September. Enabling on-
demand database computing with MIT SuperCloud database management system. In
High Performance Extreme Computing Conference (HPEC), 2015 IEEE (pp. 1-6). IEEE.
[7] Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., Geschwind,
J.F. and Lin, M., 2015. How I do it: a practical database management system to assist

9DATABASE DESIGN AND DEVELOPMENT
clinical research teams with data collection, organization, and reporting. Academic
radiology, 22(4), pp.527-533.
[8] Souza, R., Silva, V., Oliveira, D., Valduriez, P., Lima, A.A. and Mattoso, M., 2015.
Parallel execution of workflows driven by a distributed database management system. In
ACM/IEEE Conference on Supercomputing, Poster.
[9] Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson
Education India.
[10] Arulraj, J. and Pavlo, A., 2017, May. How to build a non-volatile memory database
management system. In Proceedings of the 2017 ACM International Conference on
Management of Data (pp. 1753-1758). ACM.
[11] Gubanov, M., Jermaine, C., Gao, Z. and Luo, S., 2016. Hybrid: A large-scale linear-
relational database management system. In MIT Annual DB Conference.
[12] Coronel, C. and Morris, S., 2016. Database systems: design, implementation, &
management. Cengage Learning.