Database Design and Development Project: Relationships and ERDs

This assignment focuses on database design and development, covering different types of relationships and their applications. It explores one-to-one, one-to-many, and many-to-many relationships with examples, explaining their appropriate use, differences, and areas of utility. The project also delves into Entity Relationship Diagrams (ERDs), detailing their purpose and how they aid in database creation. Furthermore, the assignment involves designing a movie database, including table creation with attributes, primary and foreign keys, and an ERD to visually represent the database structure. The student provides detailed examples and explanations, demonstrating a comprehensive understanding of database concepts and practical application.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database design and Development
Name of the Student
Name of the University
Authors note

1DATABASE DESIGN AND DEVELOPMENT
Different types of relationships and example
One to one relationships
There are mainly three types of relationship exists which are namely one –to-one
relationship, one-to-many relationship and finally the many to many relationship.
One-to-one relationship: For two tables, they said to have some one-to-one relationship
among their attributes. This interprets that a single record in the one table is related to a
single record in the second table and vice versa.
For a database for employees if there are two tables that contains the completely normalized
tables then we can consider the employee and salary tables of this database;
Employee table
Empid Empname Empaddres Empcontact Hiredate
1 Jacob Lower manhattan 1478524562 26/11/1990
2 Wicker LA 7418529634 25/04/2014
Salary table
Empid Salary ExtraHour Rate
2 25000 60
1 20000 45
From the above two tables it can be stated that, even though the attributes and fields in above
two tables could be managed and stored in a single table but breaking them into multiple
tables enhances the security of the database the tables are designed in this way. In this way

2DATABASE DESIGN AND DEVELOPMENT
the data of the salary table can be structured in such a way that this tables can be viewed by
only some authorized personnel in the organization. In case of the EMPLOYEES table the
empid field is related to a single row of data in the salary table which can be viewed only by
authorized personnel. According to the definition of the tables the users will be able to store
only one record for a given employee in the salary table. Therefore, there is a distinct one-to-
one relationship among the records in the EMPLOYEES and SALARY table.
One-to-many relationship
The many-to-many relation among the tables in a database is defined when one
record of a table is connected to the one or more records in one more related table. This
relationship is established by storing value or record for a particular field. This key is usually
Primary Key in the table. Record or the attribute values of ‘One’ side of the one to many
relationship in a field is stored in related record of the many side of the relation table. The
connecting tables are represented as the Foreign Keys. In case of the one to many relations
the foreign key not needs to be unique for different tables. One of this tables is known as the
Primary Table and another one is foreign table.
For business organization the customer and order tables can be considered as the
examples of the one to many relationship. In this scenarios primary key for the customers
table is the customerID which contains only the unique values to maintain data consistency
and integrity. Again the foreign key in order table is the customerID. This foreign key is
helpful in allowing the users with the multiple instances for the same foreign key.
This one-to-many relationship is helpful in retrieving the connected/related records
from the tables whenever the value for the Customer ID stored in order table matches with
the values of customerid in customer table. In other words it can be stated that, there may
exist multiple orders for a specific customerid in order table.

3DATABASE DESIGN AND DEVELOPMENT
Many-to-many relationship
In relational database many-to-many relationship are used in order to store multiple
record in a table that are related to the multiple records in the other table of the relation. As an
example the scenario of customer and product table can be considered. There exist many-to-
many relationship among the customers and product table. Any customer from the customer
table can order various types of products with different productid on the other hand the
product with same productid can be purchased by multiple customers.
The traditional database systems often does not allow users to implement many-to-
many relationships among the tables as they may lead to the inconsistent data stored in the
database. For this we can consider table of invoices. If multiple invoices having same
invoice number are stored in the database and the user need to get the details of a specific
invoice number and supplier then the database would not be able to determine exactly which
invoice data needs to be fetched.
A simple and easy example of the many to many relation is the student and class
tables for the database of the educational institute. As there may students had enrolled for a
multiple classes and there may be classes where multiple students are registered.
Another example of many to many relationship can be author and book table as
depicted in the below tables.
Author_id Fname Lname

4DATABASE DESIGN AND DEVELOPMENT
Bookid Title ISBN Printver
Authorid Bookid
From the above table, it can be stated there are authors that may have written books in
collaboration and may not have present their names for some specific books. Then there are
books which are written by multiple authors and authors whose id is present for multiple
books.
ERD
The ERD is visual representation of different data objects for a database for a
particular scenario and the relation between the different attributes in order to show the
interaction between them. The ERD is very useful in order to depict a high-level logical
model. This high level design can lead to better conceptual design of the databases. From this
stage it is possible to normalize the initial tables so that the actual database can be designed
optimally so that the storage can be used efficiently and avoid the inconsistent and redundant
data.

5DATABASE DESIGN AND DEVELOPMENT
Proposed ERD for the Movie database

6DATABASE DESIGN AND DEVELOPMENT