Essay
Unit 18 Database Design
P1, P2, P3, P4, P5, P6, M1, M2, M3, D1, D2
[Show more]
Connected book
Book Title:
Author(s):
- Edition:
- ISBN:
- Edition:
8 reviews
By: ebsahin1 • 5 year ago
By: xeon • 5 year ago
What was the issue with the work?
By: million • 5 year ago
By: xeon • 3 year ago
Hey, thanks for your review. How can the document be improved?
By: jwanakim • 6 year ago
By: xeon • 6 year ago
Thanks for your review.
By: prianpatel678 • 6 year ago
By: xeon • 6 year ago
What was the issue with the work? Thanks.
By: saffron_leo • 6 year ago
By: xeon • 6 year ago
Hey, how can it be improved? Thanks.
By: JamesWhite • 7 year ago
relationships in the database seem to be fine, assignment criterias seem to be met.
By: aliali2 • 7 year ago
By: xeon • 7 year ago
Hello aliali2, just wondering if you have any suggestions for improvement of my unit 18 database design. Thanks, Raj.
Content preview
UNIT 18P1
The purpose of databases
The purpose of databases is to eliminate data redundancy, meaning the same piece of data will
not be stored in more than one place, because duplicaton of data will waste storage space on
the hard-drive or storage device, but this could also cause inconsistencies. Databases make it
easier to fnd informaton, as well as deletng or adding data. Databases are mult-accessible
meaning more than one person can view data at one tme.
Comparing relatonal databases to paper based systems.
A relatonal database is used to hold more than one table, in which tables are connected to
each other with the use of key felds. For example, a college database could have three tables:
College student – When a student signs up to a college a record will be created. It will save
informaton such as frst name, surname, date of birth and a unique student ID which will be on
their student card also to sign in to the college.
College library – the books within the college library will have a record, and will include
informaton about the book, such as ttle, and author, and unique ID for each book as it is likely
there will be duplicates of books within the library.
Lending – When a student wants to borrow a book, the lending table will save the student’s
unique student ID and the book’s unique ID and store it in a table. The table can also include
extra informaton for example when the book should be due back.
There are many advantages to using relatonal databases, for example the student’s and book
detail’s will only be required to be entered once into the database. Therefore, mistakes will
happen less ofen and if a mistake was made, such as a typing mistake within a student’s name,
correctng this will correct the mistake data-base wise, meaning the database at the college
library will now have the correct name. Queries could also be quickly being made and reports
created easily, for example how many books in the college were borrowed this year in
comparison to last year. There is no duplicaton of data, which will keep the fle of the database
to a minimum, therefore it will not take long to load-up the database. The employees at the
library can easily search details by book ID and student ID to fnd out what may have happened
to a book, as these identfcatons will be connected using a relatonship.
Define entty and attribute
,Enttes are tables within a database which store informaton about objoects which will be
recorded, for example students at a college. Atributes are presented by columns and/or felds
of a table. These are propertes of an entty, specifc informaton of the enttes which is
differentates enttes. For example, name, and addresses of employees at the pizazaa company.
The use of key fields and foreign keys in linking tables.
Primary keys are used as unique data which allows the user to identfy and piece of record
within a database. A foreign key is used when a primary key is duplicated to another table
within the database, which will create a relatonship, in which this is now a foreign key. This
allows a user which uses an electronic database to gain data as fast as possible and efciently as
possible.
The method of creatng many too many relatonships
In a college library database, a book may have been writen by one or more authors, although
an author can write zaero or books this relatonship will be known to many-to-many. For
example, in a product sales database, it will begin with two tables: orders and products. The
table products will store informaton about the products (such as product name, descripton..
The table order’s will store customer’s orders (customerID, date ordered, data required..
Although, here we would be unable to store the items ordered in the orders table as we are
unaware of the amount of columns to reserve for items and we are also unable to save the
order informaton within the products table.
To be able to support many-to-many relatonships, you would need to create a third table
“Order informatonn in which each row will present an item for a specifc order. For the order
informaton table, the primary key will contain two columns, productID and orderID, this will be
used to identfy every row unequally. The order informaton table will reference the table
‘orders’ and the table ‘products’ meaning these are foreign keys for the table ‘order
informaton’.
The use of diferent Data types
Within a database it is important to use the correct datatypes for each variable, for example
having a variable as integer to store informaton about the descripton of a pizazaa in a database,
doing this will result in errors within a database. It is also important to use the most effectve
data type for a database, for example using the data-type long-text for the product name of a
pizazaa, in which short-text could have been used to save this, using long-text is a waste of
memory, and databases are supposed to save data efcient as possible.
, Describe the use of queries to extract data from linked tables
Queries are used to get specifc data from the database tables, data which you want to see
which is across many tables, queries allow you to see all this within one single datasheet.
Queries, allow a user to flter data shown, to the records they specifcally want.
P2
Data dictonary, table structures, fields and propertes
Table name: Branch
Table propertes: None
Fieldname Datatype Field sizae and/or propertes
BranchID Autonumber Field sizae 20 / Primary key
BranchName Short Text Field sizae 15
BranchAddress Short text Field sizae 15
Branch_PostCode Short text Field sizae 15
Table name: Customer
Table propertes: None
Fieldname Datatype Field sizae and/or propertes
CustomerID Autonumber Field sizae 20 / Primary key
Last_name Short text Field sizae 15
First_name Short text Field sizae 15
Table name: Employees
Table propertes: None
Fieldname Datatype Field sizae and/or propertes
EmployeeID Autonumber Field sizae 20 / Primary key
FirstName Short text Field sizae 15
LastName Short text Field sizae 15
DOB Date/tme Field sizae 10
Address Short text Field sizae 15
TelephoneNumber Short/text Field sizae 15
Table name: Order
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller xeon. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for £10.49. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
66579 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy revision notes and other study material for 14 years now