Normalisation Flashcards
(21 cards)
Normalisation. What is it and how do you normalise data?l
Normalisation is rules of one.
One type of items = one entity or table.
One item = row
One description = attribute.
Every attribute must describe the entity.
Normal forms describe ways these rules fan be broken.
Primary table - primary key
Secondary table - foreign key
This is like the parents/child relationship between two tables
Primary table will always have a primary key. Secondary table will always have a foreign key.
Cardinality. Is the number of objects that may exist on each side of the relationship. Is the cardinality one to one, one to many? Cardinality is described as either 0, 1 or infinity.
Optionality. Is whether the relationship is mandatory or optional.
Referential Integrity. Enforcing foreign key restraint. If there is a value in the foreign key there has to be a matching value in the primary key.
An example of this.. If there’s a paycheck there has to be an employee for that paycheck to match up to.
Studied advanced database. Describe 3 features of the advanced database that you have considered which make a relational database unsuitable for the advanced application.
The need for the database to create new links between the data
The need for the database to answer what if questions based on incomplete answers
The ability of the database to explain the hypothesis it is currently working with when going through a diagnosis. So with the data involved there will be a way to find marketing trends for example.
The ability to backtrack and continue using a new hypothesis if neccessary
Define the term database.
A structured collection of related data
Explain what you understand by the term database and gives examples to illustrate
The entities, attributes and relationships which comprise a subset of the database for a particular application
Normalisation rules
1f ~ 2f no partial key dependency
With unnormalised data, anomalies can occur. What anomalies can occur and what is an example of them?
6 marks
Addition anomaly
Deletion anomaly
Update anomaly
SQL
Insert a new partial record into the customer table using the data;
customer id 1234
customer forename Glenn
Customer surname Newman
INSERT INTO customer (customer_ID, forename, surname)
VALUES (“1234”, “Glenn”, “Newman”);
What does cardinality and optionality mean?
Cardinality is the degree of the relationship. 1-1, 1-m, m-m.
Optionality identifies whether every occurrence of the entity must be (or may be) related to occurrences of the entity at the other end of the relationship.
Choose3 relationships from the ERD diagram and discuss their cardinality and optionality
Describe the degree of the relationship?
Describe the
Define Validation
Validation is concerned with checking that the data entered into a database is reasonable (complete accuracy may not be possible on data entry but many mistakes can be eliminated with the appropriate use of validation techniques)
Define integrity
Integrity a database is said to have integrity if it has not been corrupted after initial data entry. It can include processes to check the integrity of the database links as well as the integrity of the data in the database
Define Security
Guarding against accidental or malicious loss, damage or disclosure of the data in the database
Identify 3 different methods of validation, explain how the validation would work and give appropriate examples of their use
Ensure description and example
is included
Range check - checking a value lies between a given min and max Could be used to check DOB lies between today and 110 years ago
Look up - check a valid already exists in another table - for example, when inputting a foreign key - to avoid creating an order for a customer that doesn’t exist
Picture/input mask - to check format of the input conforms to that required eg a customer reference AAA99 - SMI01
Briefly discuss 5 appropriate situations where database security needs to be considered
Accidental disclosure of info Malicious damage (vandalism) Accidental damage (electrical problem or environmental problem) Accidental deletion of info Social engineering or hack attack
Discuss how data locks can be used as a means of solving the problems of concurrency. Include an explanation of granularity.
A lock is a means of claiming usage rights on a resource. There can be several different types of resources that can be locked and several different ways of locking those resources. A user can apply four different levels of locking: Exclusive, write, read and access. The granularity refers to how finely the locking is applied to the data. The lock can be applied to the following object: Database, table, view and row.
the following ta me is in 1nf. What tables would you have as a result of carrying out the appropriate actions at 2nf, explain and justify what you have done.
ORDER ordernumber-- customer ref order date dispatch date
ORDER-LINE Order no--* Stock code-- Stock description Quantity in stock Quantity ordered
ORDER order number-- customer ref order date dispatch date
ORDERLINE
Order No–*
Stock code*
Quantity ordered
STOCK
stock code–
stock description
quantity in stock
Include explanation that as order has a simple
key there is no partial key dependency
Explain which fields are partially dependent
Deadlock or deadly
embrace can occur as a result of applying data locks, describe the problem of deadlock
Deadlock occurs when the transaction places a lock on resources A, then needs to lock resources B. But resource B has already been locked by transaction 2, which in turn needs to place a lock on resource A. This state of affairs is called a deadlock or a deadly embrace.
How does the use of 2-phase locking overcome the problem of deadlock?
Phase 1 - all required resources are collected, if any are not available then resources are released and collection begins again after a random time interval.
Phase 2 - Once all resources are collected they are locked for update, transaction occurs and then resources unlocked and released
What is a transaction and why it is important?
A transaction is a logical unit of work. It is both the unit of work and the unit of recovery. The statements nested within a transaction must either all happen or none happen. Transactions are atomic: there is no such thing as a partial transaction. Transactions have a defined start and end.
In a mail-order company 2 employees are updating the company’s multi-user database system. A customer starts the day with an account balance of £154.00 (owing to the company). Employee A processes a new order for this customer which would add a further £220 to their account. Employee B processes a payment from the customer for £154.00. At the end of the day the customer’s account is showing a £0 balance. Given that there has been no other updates to the customer account and that the employees themselves have not made any mistakes in the update process, discuss the problem that the company’s multi-user database system seems to be experiencing.
The problem is concurrency.
Both employees must have been updating same customer account at the same time.
Both employee would have taken a copy of the original of the original customer’s account prior to the updates.
Employee A saved their updated record first showing an increased Account value of £374. Employee B would then have saved their update showing the account at £0 - This update will overwrite the update of employee A
Explain what you understand by the three terms Database Management System, Data Independence and Data Redundancy(6 marks)
DMS’s are software that handles the storage, retrieval, and updating of data in a computer system for example Microsoft Access
Data Independence is the type of data transparency that matters for a centralized DBMS. It refers to the immunity of user applications to changes made in the definition and organization of data. Physical data independence deals with hiding the details of the storage structure from user applications.
Data Redundancy is a condition created within a database or data storage technology in which the same piece of data is held in two separate places. This can mean two different fields within a single database, or two different spots in multiple software environments or platforms.
