Hdcse4005 42 38

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 35

ST/HDCSE/CMU/42/38 J,A,I.D.

Jayasinghe

i
HDCSE4005 - Database Design
Development

Cardiff student number:


ICBT Student number: ST/HDCSE/CMU/42/38
University Number: cQ_8(3Gi

ii
Acknowledgement
I would like to express my deepest gratitude to Ms. Kalani Kokawela, my module
lecturer, for her continuous guidance, support, and encouragement throughout the
completion of this assignment. Her insightful feedback and expertise in database
design and development have been invaluable in helping me understand the core
concepts and successfully apply them to this project.
I would also like to extend my appreciation to my peers and colleagues for their
cooperation and assistance. Lastly, I would like to thank my family for their
unwavering support and encouragement during this process. This assignment has been
an enriching learning experience, and I am grateful to everyone who contributed to its
successful completion.

iii
Executive summary
This report presents the design and development of a Database Management System
(DBMS) for "Mail Com," a mailing system proposed by Infor Pvt Ltd. The objective
was to create a system that could efficiently manage large volumes of data related to
user accounts, emails, attachments, and domain servers.
The report begins by analyzing different data models, comparing their structures,
advantages, and disadvantages in the context of the Mail Com system. Based on this
analysis, the Relational Data Model was selected as the most suitable for its
flexibility, scalability, and ability to handle complex relationships. The design
includes key database components, such as users, mailboxes, emails, and servers,
ensuring that the system can efficiently manage multiple interactions between these
entities.
An Entity-Relationship Diagram (ERD) was developed to visualize the relationships
between the entities, identifying the important primary and foreign keys and other
attributes. The relational schema was mapped from the ERD, and the database was
normalized up to the Third Normal Form (3NF) to minimize redundancy and improve
efficiency.
Furthermore, SQL queries were designed to meet essential functional requirements,
such as retrieving user information, managing emails, and handling domain-related
operations. The report also includes a test plan and strategy to ensure the integrity,
performance, and security of the system. The tests were aimed at verifying CRUD
operations, database relationships, and overall functionality.

iv
Contents
Assignment Cover Sheet................................................................................................1

Acknowledgement..........................................................................................................2

Executive summary........................................................................................................3

List of figures.................................................................................................................5

List of tables...................................................................................................................6

Introduction....................................................................................................................7

Task 1.............................................................................................................................8

What Is A Data Model?..............................................................................................8

Comparison of Different Data Model........................................................................8

Hierarchical Data Model........................................................................................8

Network Data Model..............................................................................................9

Relational Data Model...........................................................................................9

Object-Oriented Data Model................................................................................10

Task 2...........................................................................................................................12

Task 3...........................................................................................................................13

Relational Schema....................................................................................................13

Schema Normalization.................................................................................................13

Task 4...........................................................................................................................16

Task 5...........................................................................................................................26

Task 6...........................................................................................................................28

Test Plan...................................................................................................................28

Conclusion....................................................................................................................32

Reference........................................................................................................................x

Appendix.......................................................................................................................xi

v
List of figures
Figure 1 Entity relationship diagram............................................................................12
Figure 2 Relational Schemas........................................................................................13
Figure 3 Create database..............................................................................................16
Figure 4 Use database..................................................................................................16
Figure 5 Create domain table.......................................................................................17
Figure 6 Create sever table...........................................................................................17
Figure 7 Create mail boxes table..................................................................................18
Figure 8 Create email table..........................................................................................18
Figure 9 Create user table...........................................................................................19
Figure 10 Create contact table......................................................................................19
Figure 11 Create user table..........................................................................................20
Figure 12 Add rows user table.....................................................................................20
Figure 13 Select all from user......................................................................................21
Figure 14 Add rows domain table................................................................................21
Figure 15 Select all from domain.................................................................................22
Figure 16 Add rows sever table...................................................................................22
Figure 17 Select all from sever....................................................................................23
Figure 18 Add rows e mail...........................................................................................23
Figure 19 Select all from e mail...................................................................................24
Figure 20 Add rows attachment table..........................................................................24
Figure 21 Add rows contact table................................................................................25
Figure 22 Select all from contacts................................................................................25

vi
List of tables
Table 1 Comparison of data models.............................................................................10

vii
Introduction
Infor Pvt Ltd, a leading IT company in Sri Lanka, is developing a new mailing system
called "Mail Com" to handle the enormous volume of data related to email accounts,
messages, attachments, and contact lists. The objective of this report is to design and
develop a Database Management System (DBMS) that meets the needs of the Mail
Com system using relational database technology.
In the fast-evolving digital landscape, managing large amounts of data efficiently is
crucial. A well-structured DBMS will ensure that Mail Com can handle high volumes
of emails while maintaining data integrity, security, and ease of access. The database
must support core functions such as user registration, mailbox management, and email
transactions (sending, receiving, and deleting emails), as well as the management of
servers and domains.
This report covers the entire database development process, beginning with the
comparison of different data models, leading to the selection of the relational model.
It includes the creation of an Entity-Relationship Diagram (ERD) to represent the
system's entities, attributes, and relationships, followed by the development of a
normalized relational schema. SQL queries were designed to meet functional
requirements, and a detailed test plan was created to ensure the system’s functionality
and performance.
The aim of this project is to provide a scalable, reliable, and efficient DBMS solution
that will support the ongoing operations of Mail Com, ensuring it can manage user
accounts and email transactions effectively in a dynamic and data-intensive
environment.

1
Task 1
What Is A Data Model?
A data model is a conceptual framework that defines how data is structured, stored,
and managed in a database system. It represents the relationships between different
data elements and defines how the data is organized to meet specific system
requirements (Elmasri & Navathe, 2016). Data models are essential for database
design as they establish the foundation for efficient data management, retrieval, and
manipulation.
In the context of Mail Com, a data model will provide the structure for storing and
managing information related to users, domains, servers, mailboxes, emails, and
attachments. The choice of an appropriate data model is crucial to ensuring the
system's ability to handle large volumes of data and support the necessary operations
like sending, receiving, and managing emails.
Comparison of Different Data Model
Different data models have been used over time to manage and structure data in
database management systems. In the context of the proposed "Mail Com" mailing
system, selecting the appropriate data model is crucial for handling the large volume
of user data, emails, and attachments. Below is a description of the most common data
models, comparing their structures, advantages, disadvantages, and suitability for the
Mail Com system.

Hierarchical Data Model


The Hierarchical Data Model organizes data in a tree-like structure, where each child
node has a single parent, creating a strict hierarchy (Elmasri & Navathe, 2016). This
structure is relatively simple to understand and is often used in applications where
data naturally forms a hierarchy, such as file systems or organizational charts.
However, the hierarchical model lacks flexibility because it enforces a rigid parent-
child relationship. In this model, each child can have only one parent, which limits the
model’s ability to represent more complex relationships, such as many-to-many
relationships (Silberschatz, Korth & Sudarshan, 2019).

2
Suitability for Mail Com: The hierarchical model is not ideal for Mail Com, as emails
involve complex relationships where a single user can send and receive emails from
multiple other users. The rigidity of the hierarchical structure makes it unsuitable for
the dynamic and many-to-many relationships required for a mailing system.

Network Data Model


The Network Data Model improves upon the hierarchical model by allowing records
to have multiple parent and child nodes, creating a more flexible graph-like structure
(Elmasri & Navathe, 2016). This model can represent more complex relationships and
is useful for systems with intricate linkages between data entities.
Despite its improved flexibility, the network model introduces complexity in terms of
design and maintenance. It requires complex pointers between records, which can
make database management cumbersome (Silberschatz, Korth & Sudarshan, 2019).
Suitability for Mail Com: While the network model could theoretically handle the
relationships between users, mailboxes, and emails better than the hierarchical model,
its complexity makes it a less practical option for Mail Com. The added maintenance
difficulty makes it less suitable for a modern, scalable email system.

Relational Data Model


The Relational Data Model organizes data into tables (relations), with each table
consisting of rows and columns. Relationships between tables are managed using
foreign keys, which allows for flexibility in representing complex data relationships
(Silberschatz, Korth & Sudarshan, 2019). Relational databases also use Structured
Query Language (SQL) to manipulate and retrieve data efficiently, making this model
popular for a wide range of applications.
The relational model offers a high degree of scalability and flexibility, making it well-
suited to applications that handle large volumes of data with multiple relationships. It
also provides robust support for data integrity through features such as primary keys
and foreign keys (Elmasri & Navathe, 2016).

Suitability for Mail Com: The relational data model is the best fit for Mail Com. It
allows the system to efficiently manage complex relationships between users,
3
mailboxes, and emails while ensuring data integrity and scalability. SQL can be used
to manage the system efficiently, making this model both practical and reliable for the
mailing system.
Object-Oriented Data Model
The Object-Oriented Data Model integrates object-oriented programming principles
with database management. Data is stored as objects, which include both the data and
the methods that act upon it (Elmasri & Navathe, 2016). This model is particularly
useful for applications that handle multimedia data or complex data types, such as
images, video, and audio.
However, the object-oriented model is not as widely used or standardized as relational
databases. It lacks the maturity and widespread support of relational systems, which
can make implementation and integration more challenging (Silberschatz, Korth &
Sudarshan, 2019).
Suitability for Mail Com: While the object-oriented model could be beneficial for
handling email attachments or multimedia data, it is not as efficient as the relational
model for handling the core email operations in Mail Com. The mailing system is
primarily text-based, and the relational model provides a better balance of efficiency,
scalability, and simplicity.
Table 1 Comparison of data models

Data Model Structure Advantages Disadvantages Suitability for


Mail Com
Hierarchical Tree-like structure Simple to Lacks Not ideal, as
with parent-child understand and flexibility; child emails involve
relationships implement for nodes can only many-to-many
hierarchical data have one parent relationships
Network Graph-like structure More flexible Difficult to Could represent
where records can than design and relationships
have multiple parents hierarchical; can maintain due to better than
and children represent complex hierarchical, but
complex pointers too complex for
relationships this application
Relational Tables (relations) Highly flexible, May become Best fit for Mail
with rows and scalable, slow with very Com due to
columns; foreign keys supports SQL large datasets if flexibility,
to establish for efficient data not optimized scalability, and
relationships management SQL support
Object- Data represented as Handles Lacks Could handle
Oriented objects similar to complex data standardization attachments well,
object-oriented types and and maturity but not as efficient
programming relationships; compared to for core email
4
integrates well relational operations
with object- databases
oriented
languages

5
Task 2

Figure 1 Entity relationship diagram

6
Task 3
Relational Schema
The relational schema given below describes the structure and relationships of the
database components.

Schema Normalization

Figure 2 Relational Schemas


First Normal Form (1NF):
There are no repeating groups or arrays (each value is atomic).
Each entry in the table is unique.
Your schema already satisfies 1NF because:
Each column contains atomic values (e.g., each email_ID, server_name, and
user_name holds a single value).
Each row is uniquely identifiable by a primary key (e.g., domain_ID, server_ID,
email_ID).

Second Normal Form (2NF):


It is in 1NF.

7
All non-primary key attributes are fully functionally dependent on the primary key. In
other words, no partial dependency exists, meaning no attribute depends on only a
part of a composite primary key (if there is one).
When going through my database table:
Domain: Each non-key attribute (domain_name, domain_type, domain_description)
depends only on domain_ID. No partial dependencies exist.
Server: Each non-key attribute (server_name, server_type, server_description,
server_address) depends fully on server_ID. Additionally, server_domain_ID refers to
domain_ID, so there’s no partial dependency.
User: All attributes like name, email, date_of_birth, phone_no, and gender depend
fully on user_ID. No partial dependencies here either.
MailBox: mailbox_description, mailbox_type, and mailbox_user_ID are fully
dependent on mailbox_ID.
Email: Attributes like subject, message, and sender_user_ID depend fully on
email_ID.
Adjustments for 2NF:
My schema already satisfies 2NF because:
No composite primary keys with partial dependencies.
All attributes depend fully on the primary key in each table.
Third Normal Form (3NF):
A table is in 3NF if:
It is in 2NF.
There are no transitive dependencies. This means that no non-primary attribute
depends on another non-primary attribute.
When going through my database table:
Domain Table: No transitive dependencies. All attributes are directly related to
domain_ID.
Server Table: Similarly, all attributes depend directly on the server_ID.
User Table: No transitive dependencies. Attributes like email, name, and gender are
directly dependent on user_ID.
MailBox Table: mailbox_description, mailbox_type, and mailbox_user_ID are
directly dependent on mailbox_ID.

8
Email Table: There is a potential transitive dependency here:
sender_user_ID depends on email_ID, but sender_user_ID is a foreign key that refers
to the User table, and email_ID relates to the email details. This is an expected
relationship in this case, and it doesn’t violate 3NF because the dependency directly
links email and user information.
Adjustments for 3NF:
schema already satisfies 3NF as the attributes have no transitive dependencies.

9
Task 4
Create database

Figure 3 Create database

Use database

Figure 4 Use database

10
Create domain table

Figure 5 Create domain table

Create sever table

Figure 6 Create sever table

11
Create mail boxes table

Figure 7 Create mail boxes table

Create email table

Figure 8 Create email table

12
Create user table

Figure 9 Create user table

Create contact table

Figure 10 Create contact table

13
Create user table

Figure 11 Create user table

Add rows user table

Figure 12 Add rows user table

14
Select all from user

Figure 13 Select all from user

Add rows domain table

Figure 14 Add rows domain table

Select all from domain


15
Figure 15 Select all from domain

Add rows sever table

Figure 16 Add rows sever table

Select all from sever

16
Figure 17 Select all from sever

Add rows e mail

Figure 18 Add rows e mail

Select all from e mail

17
Figure 19 Select all from e mail

Add rows attachment table

Figure 20 Add rows attachment table

Add rows contact table

18
Figure 21 Add rows contact table

Select all from contacts

Figure 22 Select all from contacts

19
Task 5

20
21
Task 6
Test Plan
1. Objective:
The goal is to check that the "Mail Com" database works correctly.
We want to make sure all database actions, like creating, reading, updating, and
deleting data (CRUD operations), work as they should.
Also, we need to confirm that all the connections between tables (like Domain,
Server, User, MailBox, and Email) are correct and the database meets business needs.

2. Scope:
This test focuses on checking that data is correct across all the tables, especially for
actions like adding, changing, and deleting data.
It will also check the business rules, like stored procedures and triggers, to see if they
behave properly.
Lastly, it tests how well the database performs when handling different amounts of
data, and checks if the relationships between tables stay consistent and accurate.

3. Testing Criteria:
Functionality Testing: We will test if we can create, read, update, and delete data from
all tables and see if custom procedures work correctly.
Integrity Testing: We will make sure that the relationships between different tables
(like Domain, Server, User, MailBox, and Email) follow the rules, meaning the
foreign key constraints should work correctly.
Performance Testing: We will see if the database can handle the expected amount of
data without slowing down.
Security Testing: We will check if users with different roles have the right level of
access, so they can only do what they are allowed to do.

4. Testing Environment:
Database Server: The tests will be done on SQL Server.
Tools: We will use SQL Server Management Studio (SSMS) to run the tests.

22
Test Data: We will create at least 10 sample records for each table (like Domain,
Server, User, etc.) to run the tests.
Test Strategy
1. Test Levels:
Unit Testing: We will check individual parts of the database, like each table, stored
procedure, trigger, and function, to make sure they work correctly on their own.
Integration Testing: We will test how different parts of the database (like tables) work
together. This includes checking foreign key relationships and what happens when we
delete or update linked data.
System Testing: We will make sure the whole database system works as expected,
with all features functioning properly.
2. Test Data Management:
We will create fake data for different parts of the database, like Domain, Server, User,
MailBox, and Email.
The data will include examples of real-world scenarios, such as valid and invalid
entries, data that tests limits (like the oldest or largest allowed values), and different
user profiles to cover a range of situations.

23
Test Case ID Description Test Steps Expected Result Status
TC_01 Validate creation of a Insert a new New domain record is Pass
Domain Domain
created with correct
record.
attributes.
TC_02 Validate deletion of a Attempt to Deletion fails due to foreign Pass
Domain delete a key constraint, or cascades as
domain with per design.
associated
servers.

TC_03 Verify domain-server Query to Correct server records are Pass


association retrieve linked with the domain.
Domain and
Server data
based on
Domain_ID.

TC_04 Verify Server creation Insert a new Server is created and linked Pass
Server record to the correct domain via
associated Domain_ID.

with an
existing
Domain.

TC_05 Verify MailBox creation Insert a new MailBox is created and Pass
for a user MailBox linked to the correct
record for a User_ID.
user.

TC_06 Validate email sending 1. Insert a new Email is created and linked to Pass
Email record.
process (Email -> the correct mailbox via
2. Check the
24
MailBox) MailBox_ID MailBox_ID.
association.

TC_07 Verify email subject and Query to Correct subject and message Pass
message retrieval retrieve details are returned for the
Subject and email.
Message for a
specific email.

TC_08 Validate foreign key Attempt to Deletion fails due to foreign Pass
constraint between User delete a User key constraint or cascades
and MailBox with linked based on the design.
MailBox.

TC_09 Verify Attachment Insert a new Attachment is correctly Pass


creation and association Attachment created and linked to the
with Email linked to an email.
existing email.

TC_10 Test referential integrity Attempt to Deletion fails due to foreign Pass
between Email and User delete a user key constraint, or emails are
who has sent handled correctly based on
emails. the design (cascade or
restrict).

25
Conclusion
In this report, we developed a comprehensive database management system (DBMS)
for "Mail Com," a mailing system for Infor Pvt Ltd. The database was designed using
the Relational Data Model due to its flexibility, scalability, and ability to manage
complex relationships between users, emails, and attachments. The relational model
was chosen over hierarchical and network models, which are more rigid and difficult
to maintain for such a dynamic system. The design ensures data integrity and supports
efficient querying using Structured Query Language (SQL).
Through the process, we also drew an Entity-Relationship Diagram (ERD), which
identified all the key entities, attributes, and relationships, making sure the system
would maintain referential integrity across tables. The database schema was
normalized up to the Third Normal Form (3NF), eliminating redundancy and ensuring
the system's robustness.
SQL queries were developed to meet key functional requirements, such as retrieving
user details, email lists, and domain information. Lastly, a test plan and strategy were
devised to ensure the database functions as expected, focusing on CRUD operations,
integrity, performance, and security testing.

26
Reference
Elmasri, R. & Navathe, S.B., 2016. Fundamentals of Database Systems. 7th ed. Pearson.

Silberschatz, A., Korth, H.F. & Sudarshan, S., 2019. Database System Concepts. 7th ed.
McGraw-Hill.

Date, C.J., 2004. An Introduction to Database Systems. 8th ed. Addison Wesley.

Connolly, T. & Begg, C., 2014. Database Systems: A Practical Approach to Design,
Implementation, and Management. 6th ed. Pearson.

Coronel, C., Morris, S. & Rob, P., 2013. Database Systems: Design, Implementation, and
Management. 11th ed. Cengage Learning.

x
Appendix

xi

You might also like