Database Management Systems Assignment

23-03-16 Rochio Varela 13 comments

Introduction

This assignment has been made in order to understand the concepts of database management system and its features and implementation. It will help you to learn and excel student’s fundamental database concepts like ER Diagram and Normalization, and other complex concepts. One will be able to understand the some of its features like cardinality of the relation, relationship schema or structure or relations.

Assumptions

  • We have made a mediator table separately which indicates the contact_name, contact_email, contact_phone_no of supplier instead of involving these attributes in supplier table.
  • We have assumed DEO as a main authoritative entity which is connected with staff, supplier, order, event, product and customers of all types.
  • Customer will be of four types but we assumed three as there are no such attributes in the individual customer table.
  • We have assumed one to many and many to many relationships between the tables.
  • Customer will request for the products to the DEO.
  • DEO place order to the supplier for resources needs by the customer.
  • DEO will contact to the event manager for handling the event organized at the customer’s end.
  • Event manager makes arrangements by delegating duties to staff or its employees.
  • The staff is categorized into three categories that are full-time, part-time, casual basis.
Read more about Database Assignment

ERD of DEO

Database Management Systems Assignment Programming Assignments Help

Figure 1 ERD with crowfoot notation

Get Help About Android Assignment

Cardinality

Cardinality can be defined as a number of occurrences of an entity in one relation to the number of the entity in another relation. Cardinality in databases can be defined in various forms like:

  • One to one (1:1) – In one of one cardinality, one record of a table is related to one record of another table.
  • One to many (1: M) – In one to many cardinalities, one record of a table is related to many records in another table.
  • Many to one (M: 1) – In many to one cardinality, many records of a table are related to one record of another table.
  • Many to many (M: N) – In many to many cardinalities, many records of a table is related to many records in another table.

In our DEO case study these are the cardinality of relations:

DEO: Customer = 1: M

Customer: Product=M: N

Customer: Order=M: N

Staff: Event=M: N

DEO: staff=1: M

Supplier: Product=M: N

DEO: Supplier=1: M

Read more about C Programming Help

Normalization

Normalization is a process of organizing data and establishes relations between the tables into the database. This concept is used in the database to secure data and avoid redundancy and inconsistency in the data values. Normalization basically has two significances:   See more about Java Assignment Help

  • To eliminate redundant data
  • To ensure dependencies

The normalization can be achieved by:

  • 1NF
  • 2NF
  • 3NF
  • We have achieved normalization by starting address of customer and supplier entity into street, location, city, postcode and other attributes.
  • We have also made a different table to store the details of the contact person of customer and supplier table.
  • In the product table, the product type has several products so instead of writing them as comma separating values we have made an entry for each product type category.
  • So 1NF is applied to the address of customer and supplier table while 2NF is applied in Product table and 3NF is applied to the referral table by maintaining a different table for the {contact name, phone_no, email_id}.

 Read more about PHP Assignment Help

Elaborated form of normalization through screenshots

Database Management Systems Assignment Programming Assignments Help

Figure 2 Correct1NF

Database Management Systems Assignment

Figure 3 Incorrect 1NF

Database Management Systems Assignment

Figure 4 Correct 2NF

Get Help About Basic Web Designing Help

Dependency diagram

Database Management Systems Assignment Programming Assignments Help

Database Management Systems Assignment Programming Assignments Help

Database Management Systems Assignment Programming Assignments Help

Database Implementation

Database Management Systems Assignment Programming Assignments Help

Figure 8 Customer table

Database Management Systems Assignment Programming Assignments Help

Read more about Programming Assignment Help

Figure 9 Business customer

Database Management Systems Assignment

Figure 10 School customer

Database Management Systems Assignment

Figure 11 Event table

Relational data structure (Schema)

It is described as a schematic representation of the database that includes the tables or relations, entities, attributes, briefly defined primary and foreign key and type and length of inserted data.

Entity Attributes Datatype Length Description
REFERRAL CONTACT_NAME VARCHAR 11 PRIMARY_KEY
CONTACT_PHONE_NO INT 11
CONTACT_EMAIL VARCHAR 50
STAFF STAFF_ID INT PRIMARY_KEY
TYPE VARCHAR
NAME VARCHAR
TYPE OF CERTI VARCHAR
CONTACT_ID INT
EMAIL VARCHAR
TFN VARCHAR
PAYRATE/HR INT
CUSTOMER
CUST_TYPE VARCHAR 45
CUST_NAME VARCHAR 45
CUST_ADDRESS INT 11
LOCATION INT 11
POSTCODE INT
DELIVERY_ADDRESS VARCHAR
CUST_ID INT PRIMARY_KEY
STREET VARCHAR
CITY VARCHAR
STATE VARCHAR
EMAIL_ID VARCHAR
BUSINESS_CUSTOMER BUSINESS_CUSTOMER_ID INT 11 PRIMARY_KEY
CUST_ID INT 11 FOREIGN KEY
URL VARCHAR 11
SECTOR
LEGAL_STRUCTURE INT 11
SCHOOL_CUSTOMER
SCHOOL_CUSTID INT 11 PRIMARY_KEY
LEVEL VARCHAR 45
TYPE INT 11
CUST_ID INT 11 FOREIGN KEY
 

GOVT_CUSTOMER

GOVT_CUSTID INT 11 PRIMARY_KEY
LEVEL VARCHAR
CUST_ID INT 11 FOREIGN KEY
SUPPLIER
SUPPLIER_ID VARCHAR 45 PRIMARY_KEY
BUSINESS_NAME VARCHAR 45
URL INT 11
EMAIL DATETIME
PHONE_NO
CONTACT_NAME FOREIGN_KEY
LOCATION
POST_CODE
PICKUP_ADDRESS
STREET
CITY
STATE
PRODUCT PRODUCT_ID INT 11 PRIMARY_KEY
SUPPLIER_ID INT FOREIGN_KEY
DESCRIPTION VARCHAR 45
PRICE VARCHAR 45
PRODUCT_TYPE VARCHAR 45
ORDER ORDER_ID INT 11 PRIMARY_KEY
SUPPLIER_ID INT 11 FOREIGN_KEY
PRODUCT INT 11
QTY INT 45
PRICE INT 11
COST INT
EVENT

 

 

 

 

 

 

 

 

 

CUST_ID VARCHAR 45 PRIMARY_KEY,FOREIGN_KEY
SCHEDULED_DATE/TIME DATETIME 45
EVENT_TYPE INT 11
NO_OF_PEOPLE INT 11
EXPECTED_EVENT_DURATION DATETIME
ACTUAL_EVENT_DURATION DATETIME
REQ_PRODUCT VARCHAR
STAFF_ID FOREIGN_KEY
Read more about Data Models

Conclusion

Once an individual did with this assignment he/she will well acquaint with the database concepts and its several features. One will be able to understand the concept of ER diagrams using crow’s foot notation, normalization, data dependency and database implementation.

Read more about Computer Programming Assignment

Bibliography

  • Opentextbc.ca, (2015). Chapter 12 Normalization | Database Design. [online] Available at: http://opentextbc.ca/dbdesign/chapter/chapter-12-normalization/ [Accessed 30 Dec. 2015].
  • Www2.cs.uregina.ca, (2015). Crow�s Foot Notation. [online] Available at: http://www2.cs.uregina.ca/~bernatja/crowsfoot.html [Accessed 30 Dec. 2015].
  • Sqa.org.uk, (2015). Degrees of Relationship (Cardinality). [online] Available at: http://www.sqa.org.uk/e-learning/SoftDevRDS02CD/page_44.htm [Accessed 30 Dec. 2015].
  • Studytonight.com, (2015). 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight. [online] Available at: http://www.studytonight.com/dbms/database-normalization.php [Accessed 30 Dec. 2015].

Read more about Computer Network Assignment

Programming Assignments Help provides plagiarism free assignments i.e. all our solutions are genuine and written by best technical assignment writers who have years of experience. Get a Quote now.



Discussion

  • Catering Database Management Assignment

    […] This Catering Database Management Assignment discusses developing a database for a catering company. Discerning Event Organizers (DEO) is a private company which offers catering services to a range of clients including individuals, businesses, schools and government departments. The motive to design a database that will fulfill their requirements as stated in the case study. This assignment will help the database designer to understand and apply fundamental concepts of database management system. […]

    23-03-16
  • Relational Database System Assignment

    […] Implementation of query language into the developed database management system (DDL […]

    23-03-16
  • Database Analysis and Design Assignment

    […] development is fundamental in the area of software development, Database Management Systems provide the systems, tools and interfaces by which handles the storage, retrieval, and updating of […]

    23-03-16
  • Database Development Assignment Solution - PAH

    […] Assignment is fundamental in the area of computing and ICT within organizational contexts. Database Management Systems (DBMS) provide the systems, tools and interfaces by which the organization can manage their […]

    23-03-16
  • Security Misconfiguration Assignment | Programming Help

    […] application stack, including platform, web server, application server, framework, operating system, database management and custom code. Developers and network administrators need to work together to ensure that the […]

    23-03-16
  • Features of Database Management System Assignment

    […] main purpose of database management system is to maintain data integrity in the system. But a good database management system has some other features as well […]

    23-03-16
  • Database Design Techniques Assignment Help

    […] Database management systems plays an important role in organizations as with the help of this system, an organization can maintain the details of inventory, customer, orders, empoyees, expenses etc. This stored data can be used in future as and when it is required i.e. Database Management Systems (DBMS) provide systems, tools and interfaces by which the organisation can manage their information and use it to assist in the effective decision making of the organisation. The collection of structure of the database is known as database schema. Schema specifies data, data relationships, and constraints on the data. […]

    23-03-16
  • Advantages of Database Management Systems - Ba Wire

    […] Constraints-These are some consistency rules applicable to database systems such that only correct data is entered.E.g. – Maximum marks cannot be greater than 100, ‘Issue […]

    23-03-16
  • Advantages of Database Management Systems - Announcement Press

    […] Constraints-These are some consistency rules applicable to database systems such that only correct data is entered.E.g. – Maximum marks cannot be greater than 100, ‘Issue […]

    23-03-16
  • Advantages of Database Management Systems - Aid Journal

    […] Constraints-These are some consistency rules applicable to database systems such that only correct data is entered.E.g. – Maximum marks cannot be greater than 100, ‘Issue […]

    23-03-16
  • Advantages of Database Management Systems - Basics Daily

    […] Constraints-These are some consistency rules applicable to database systems such that only correct data is entered.E.g. – Maximum marks cannot be greater than 100, ‘Issue […]

    23-03-16
  • Advantages of Database Management Systems - Audience Info

    […] Constraints-These are some consistency rules applicable to database systems such that only correct data is entered.E.g. – Maximum marks cannot be greater than 100, ‘Issue […]

    23-03-16
  • Advantages of Database Management Systems - Daily Life Balance

    […] Constraints-These are some consistency rules applicable to database systems such that only correct data is entered.E.g. – Maximum marks cannot be greater than 100, ‘Issue […]

    23-03-16
  • Comments are closed.