Data Modeling for Event Management System: ITECH 2004
VerifiedAdded on  2025/05/02
|13
|1556
|341
AI Summary
Desklib provides solved assignments and past papers to help students succeed.

ACS - ITECH 2004 DATA MODELLING
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

Table of Contents
Introduction.................................................................................................................................................3
An ER Diagram.............................................................................................................................................3
Discussion of normalization.........................................................................................................................5
Relationship with table names, attributes, primary and foreign keys.........................................................8
A database schema......................................................................................................................................9
Conclusion.................................................................................................................................................12
References.................................................................................................................................................12
Introduction.................................................................................................................................................3
An ER Diagram.............................................................................................................................................3
Discussion of normalization.........................................................................................................................5
Relationship with table names, attributes, primary and foreign keys.........................................................8
A database schema......................................................................................................................................9
Conclusion.................................................................................................................................................12
References.................................................................................................................................................12

Introduction
An Entity relationship diagram is a conceptual design of database system including all events,
entities and attributes. A complete normalize design of ER model could help a organization to
develop an effective database system for any kinds of system which is either a database or an
online portal. ABC event organizers offer services of organizing events as per request of services
by individual or corporate customers. The organization receives and delivers services by some
existing form of operation. The organization requires analyzing and designing an entity
relationship diagram to automate whole business functions and to record each and every
record of business without duplicate entries. Normalization for ER design is important because
it helps to design an efficient database which can store information without occurring of data
redundancy or duplicate problems.
An ER Diagram
The ABC event organizer has large business chain by which they offer event management
services to their individual or corporate level customers through an existing manual business
approach. Now the organization wants to implement a new system to automate the business
actions and record information by the help of an efficient database design. For which Julie
commissioned me to design a database that can assist them for management of booking,
customer and supplies information along with record of all customers and suppliers. The
organization is specialized in organizing events for wedding, christenings, birthday parties and
work functions. So she wants a system with search function to extract info of supplier of
products. So to design an efficient database it is required to analyze their current business
approach. As per analyzing and understanding following points has been discovered.
 Customer- They has two types of customers: either individual or corporate. For
individual they wants to store customer contact name, location, postal, delivery address,
email address, phone and for corporate client wants to store business name, contact
name, location, postal, delivery, email, website and phone number.
 Supplier- For supplier organization needed to store business name, name of supplier,
location, postal, delivery and phone number.
 Product- The product detail like furniture including chair, table, table cloths, cutlery and
crockery.
 Staff- For staff they want to record name, address, phone, TFN and certifications.
 Event- For an event organization wants to store location, customer, date, time, type,
number of people and staff.
Now after detailed analysis it is requires understanding some basic concept of entity
relationship model.
An Entity relationship diagram is a conceptual design of database system including all events,
entities and attributes. A complete normalize design of ER model could help a organization to
develop an effective database system for any kinds of system which is either a database or an
online portal. ABC event organizers offer services of organizing events as per request of services
by individual or corporate customers. The organization receives and delivers services by some
existing form of operation. The organization requires analyzing and designing an entity
relationship diagram to automate whole business functions and to record each and every
record of business without duplicate entries. Normalization for ER design is important because
it helps to design an efficient database which can store information without occurring of data
redundancy or duplicate problems.
An ER Diagram
The ABC event organizer has large business chain by which they offer event management
services to their individual or corporate level customers through an existing manual business
approach. Now the organization wants to implement a new system to automate the business
actions and record information by the help of an efficient database design. For which Julie
commissioned me to design a database that can assist them for management of booking,
customer and supplies information along with record of all customers and suppliers. The
organization is specialized in organizing events for wedding, christenings, birthday parties and
work functions. So she wants a system with search function to extract info of supplier of
products. So to design an efficient database it is required to analyze their current business
approach. As per analyzing and understanding following points has been discovered.
 Customer- They has two types of customers: either individual or corporate. For
individual they wants to store customer contact name, location, postal, delivery address,
email address, phone and for corporate client wants to store business name, contact
name, location, postal, delivery, email, website and phone number.
 Supplier- For supplier organization needed to store business name, name of supplier,
location, postal, delivery and phone number.
 Product- The product detail like furniture including chair, table, table cloths, cutlery and
crockery.
 Staff- For staff they want to record name, address, phone, TFN and certifications.
 Event- For an event organization wants to store location, customer, date, time, type,
number of people and staff.
Now after detailed analysis it is requires understanding some basic concept of entity
relationship model.
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

 Entity- It is a real world object having independent existence and is unique against
others.
 Attributes- The details about entity like name, salary, address, age and etc called
attributes of an entity.
 Relationship- To interconnect two tables a standard relationship has been followed that
is called relationship.
 Primary Key- Each entity consist a unique primary key for the identification of that entity
and the primary key of domain entity would be foreign key of other.
 Foreign key- A foreign key is a unique key in a table and is actually a primary key of
other used to establish a relationship among to entity.
Figure 1 ER model diagram design for database
Here the above table consists of several one primary key and several foreign key. So to
understand in detail it is required to go through each table.
 Supplier table- sup_id is primary key and rests are attributes of that table.
 Customer_individual- Here cust_indi_id is primary key and event id is foreign key
 Customer_corporate- cust_corp_id primary key is and p_id and event_id is foreign key
 Event- event_id is primary key. Cust_corp_id, cust_indi_id and p_id are foreign key
others.
 Attributes- The details about entity like name, salary, address, age and etc called
attributes of an entity.
 Relationship- To interconnect two tables a standard relationship has been followed that
is called relationship.
 Primary Key- Each entity consist a unique primary key for the identification of that entity
and the primary key of domain entity would be foreign key of other.
 Foreign key- A foreign key is a unique key in a table and is actually a primary key of
other used to establish a relationship among to entity.
Figure 1 ER model diagram design for database
Here the above table consists of several one primary key and several foreign key. So to
understand in detail it is required to go through each table.
 Supplier table- sup_id is primary key and rests are attributes of that table.
 Customer_individual- Here cust_indi_id is primary key and event id is foreign key
 Customer_corporate- cust_corp_id primary key is and p_id and event_id is foreign key
 Event- event_id is primary key. Cust_corp_id, cust_indi_id and p_id are foreign key
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

 Staff- staff id is primary key
 Products- sup_id is primary key
(GeeksforGeeks, 2019)
Many-to-many
This type of relationships adds and creates complexity in database model and throughout the
development of the system. The key entity which is used to intersect two entities and create
two new one-to-may relationships and thus this entity consist attributes of both of the two
other entities. Here in table customer_corporate table the customer can have more than one
phone number and this phone number can be used by other employee of the organization. So
the customer_name will intersect both of these two entities and remove ambiguity. Similarly in
table staff, two staff could have same name and same address. So to resolve many to many
issues a new attribute staff_id has been added.
(Ibm.com, 2019)
Discussion of normalization
It is a process of organizing data into simplest structure including all attributes to eliminate
redundancy problem that occur because of a confusing attribute or because of a table where
the table can be break in two or more new table. This is done to improve query performance.
To establish an effective relationship between tables we have to categorize database into
section. Normalization within database helps to optimize all tables and pieces of data. Here as
per described all tables are normalized well except a customer and event table. The table
customer has been normalized till third normal form of normalization.
Normalization in 1NF
In this structure a customer table has been normalized in first normal form and created two
new table customers and event table to eliminate redundancy exists.
 Products- sup_id is primary key
(GeeksforGeeks, 2019)
Many-to-many
This type of relationships adds and creates complexity in database model and throughout the
development of the system. The key entity which is used to intersect two entities and create
two new one-to-may relationships and thus this entity consist attributes of both of the two
other entities. Here in table customer_corporate table the customer can have more than one
phone number and this phone number can be used by other employee of the organization. So
the customer_name will intersect both of these two entities and remove ambiguity. Similarly in
table staff, two staff could have same name and same address. So to resolve many to many
issues a new attribute staff_id has been added.
(Ibm.com, 2019)
Discussion of normalization
It is a process of organizing data into simplest structure including all attributes to eliminate
redundancy problem that occur because of a confusing attribute or because of a table where
the table can be break in two or more new table. This is done to improve query performance.
To establish an effective relationship between tables we have to categorize database into
section. Normalization within database helps to optimize all tables and pieces of data. Here as
per described all tables are normalized well except a customer and event table. The table
customer has been normalized till third normal form of normalization.
Normalization in 1NF
In this structure a customer table has been normalized in first normal form and created two
new table customers and event table to eliminate redundancy exists.

Normalization in 2NF
The below given table customer has been normalized in two new table called event table and
product table. Means it is in 1NF and fulfill condition of second normal form.
The below given table customer has been normalized in two new table called event table and
product table. Means it is in 1NF and fulfill condition of second normal form.
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

Normalization in 3NF
In third form of normalization the customer table has been splits into three table called
customer_corporate, customer_individual and product event table. Now the tables are
completely normalized and so the whole designed ER model is also normalized which could
help to efficiently handle all type of records and also can be able to retrieve records in single
click.
In third form of normalization the customer table has been splits into three table called
customer_corporate, customer_individual and product event table. Now the tables are
completely normalized and so the whole designed ER model is also normalized which could
help to efficiently handle all type of records and also can be able to retrieve records in single
click.
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

(rarr;, 2019)
Relationship with table names, attributes, primary and foreign keys
The relationship as defined with all required naming convention for table, attributes, primary and
foreign key has been address during design of the relational model, is as follows
Relationship with table names, attributes, primary and foreign keys
The relationship as defined with all required naming convention for table, attributes, primary and
foreign key has been address during design of the relational model, is as follows

A database schema
In terms of database, a schema is a basic structure of database which represents logical and
physical view on whole database. This is the process which defines how a database will be
defines and how relationship in between data will establish. The schema for the database
design for each table is as follows:
Supplier
Query: describe supplier;
Field Type Null Key Default Extra
sup_d int(11) NO PK NULL
sup_business_name varchar(255) NO NULL
sup_name varchar(255) NO NULL
sup_location varchar(255) NO NULL
In terms of database, a schema is a basic structure of database which represents logical and
physical view on whole database. This is the process which defines how a database will be
defines and how relationship in between data will establish. The schema for the database
design for each table is as follows:
Supplier
Query: describe supplier;
Field Type Null Key Default Extra
sup_d int(11) NO PK NULL
sup_business_name varchar(255) NO NULL
sup_name varchar(255) NO NULL
sup_location varchar(255) NO NULL
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide

sup_postal_address varchar(255) NO NULL
sup_delivery varchar(255) NO NULL
sup_phone_no varchar(255) NO NULL
customer_individual
Query: describe customer_individual;
Field Type Null Key Default Extra
cust_indi_id int(11) NO PK NULL
cust_indi_name varchar(255) NO NULL
cust_indi_location varchar(255) NO NULL
cust_indi_postal varchar(255) NO NULL
cust_indi_delivery varchar(255) NO NULL
cust_indi_address varchar(255) NO NULL
cust_indi_email varchar(255) NO NULL
cust_indi_phone varchar(255) NO NULL
event_id int(11) NO FK NULL
Staff
Query: describe staff;
Field Type Null Key Default Extra
staff_id int(11) NO PK NULL
staff_name varchar(255) NO NULL
staff_address varchar(255) NO NULL
staff_phone_number varchar(255) NO NULL
sup_delivery varchar(255) NO NULL
sup_phone_no varchar(255) NO NULL
customer_individual
Query: describe customer_individual;
Field Type Null Key Default Extra
cust_indi_id int(11) NO PK NULL
cust_indi_name varchar(255) NO NULL
cust_indi_location varchar(255) NO NULL
cust_indi_postal varchar(255) NO NULL
cust_indi_delivery varchar(255) NO NULL
cust_indi_address varchar(255) NO NULL
cust_indi_email varchar(255) NO NULL
cust_indi_phone varchar(255) NO NULL
event_id int(11) NO FK NULL
Staff
Query: describe staff;
Field Type Null Key Default Extra
staff_id int(11) NO PK NULL
staff_name varchar(255) NO NULL
staff_address varchar(255) NO NULL
staff_phone_number varchar(255) NO NULL
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

staff_TFN varchar(255) NO NULL
staff_certifications varchar(255) NO NULL
Events
Query: describe events;
Field Type Null Key Default Extra
event_id int(11) NO PK NULL
event_location varchar(255) NO NULL
event_type int(11) NO NULL
event_date varchar(255) NO NULL
event_time varchar(255) NO NULL
cust_corp_id int(11) NO FK NULL
cust_indi_id int(11) NO FK NULL
p_id int(11) NO FK NULL
Products
Query: describe products;
Field Type Null Key Default Extra
p_id int(11) NO PK NULL
chair varchar(255) NO NULL
table varchar(255) NO NULL
table_cloths varchar(255) NO NULL
cutlery varchar(255) NO NULL
crockery varchar(255) NO NULL
staff_certifications varchar(255) NO NULL
Events
Query: describe events;
Field Type Null Key Default Extra
event_id int(11) NO PK NULL
event_location varchar(255) NO NULL
event_type int(11) NO NULL
event_date varchar(255) NO NULL
event_time varchar(255) NO NULL
cust_corp_id int(11) NO FK NULL
cust_indi_id int(11) NO FK NULL
p_id int(11) NO FK NULL
Products
Query: describe products;
Field Type Null Key Default Extra
p_id int(11) NO PK NULL
chair varchar(255) NO NULL
table varchar(255) NO NULL
table_cloths varchar(255) NO NULL
cutlery varchar(255) NO NULL
crockery varchar(255) NO NULL

sup_id int(11) NO FK NULL
cust_ind_id int(11) NO FK NULL
customer_corporate
Query: describe customer_corporate;
Field Type Null Key Default Extra
cust_corp_id int(11) NO PK NULL
cust_corp_name varchar(255) NO NULL
cust_corp_location varchar(255) NO NULL
cust_corp_postal varchar(255) NO NULL
cust_corp_website_url varchar(255) NO NULL
cust_corp_address varchar(255) NO NULL
cust_corp_email varchar(255) NO NULL
cust_corp_phone varchar(255) NO NULL
p_id int(11) NO FK NULL
event_id int(11) NO FK NULL
(freeCodeCamp.org, 2019)
Conclusion
ER model also called entity relationship model is a first stage of database design where all the
required table and respective attributes has been identified along with a specific primary key
and other foreign keys. A best method to design a complete efficient database is to apply
concept of normalization up to 3rd normal forms. It can help to design a database without any
redundancy and allow to store and retrieve unique record whenever access by the user. The
other approach to design a relational database is to understand the database schema for each
table, allow designing a database with identification of integer and varchar values. This
assessment has been contributed to design a normalized database with concept of primary key
cust_ind_id int(11) NO FK NULL
customer_corporate
Query: describe customer_corporate;
Field Type Null Key Default Extra
cust_corp_id int(11) NO PK NULL
cust_corp_name varchar(255) NO NULL
cust_corp_location varchar(255) NO NULL
cust_corp_postal varchar(255) NO NULL
cust_corp_website_url varchar(255) NO NULL
cust_corp_address varchar(255) NO NULL
cust_corp_email varchar(255) NO NULL
cust_corp_phone varchar(255) NO NULL
p_id int(11) NO FK NULL
event_id int(11) NO FK NULL
(freeCodeCamp.org, 2019)
Conclusion
ER model also called entity relationship model is a first stage of database design where all the
required table and respective attributes has been identified along with a specific primary key
and other foreign keys. A best method to design a complete efficient database is to apply
concept of normalization up to 3rd normal forms. It can help to design a database without any
redundancy and allow to store and retrieve unique record whenever access by the user. The
other approach to design a relational database is to understand the database schema for each
table, allow designing a database with identification of integer and varchar values. This
assessment has been contributed to design a normalized database with concept of primary key
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.

Trusted by 1+ million students worldwide
1 out of 13
Related Documents

Your All-in-One AI-Powered Toolkit for Academic Success.
 +13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
Unlock your academic potential
Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.