Information Systems and Database Development

From Computing Study
Jump to: navigation, search

Entity Relationship Theory - SQL Statements

Giving examples, explain the following terms:
Primary Key
A Primary Key is a unique identifier in a table row.

customerId
customerName
customerAddress
customerTelephone

In this example table the customerId would be the primary key because other fields could be duplicated - people can have the same name, people in the same house can have the same address or telephone number.

Foreign Key
A Foreign Key is an attribute in a table that is a Primary Key from another table. creating a relationship between the two tables.

orderId
orderDate
customerId

In this example table the orderId is the Primary Key and the customerId is the Foreign Key linking the order with a customer.

Giving examples, describe what is meant by the term compound key and explain how these keys relate to many-to-many relationships
A compound key is two or more attributes which, when put together, uniquely identify a row in the table and are used as the Primary (compound) Key.

orderId
stockId
orderQuantity

In this example the orderId and stockId attributes are both Foreign Keys from other tables, and when linked form a compound primary key in an orderDetails table. In this way an order can hold many items of stock and a stock item can be on many different orders.

Write an SQL/DDL statement that will create an Oracle Database table "usedCars" that contains data on second hand cars being sold by a dealership.

CREATE TABLE usedCars (
    car_id VARCHAR2(8) PRIMARY KEY,
    car_make VARCHAR2(25) NOT NULL,
    car_model VARCHAR2(25) NOT NULL,
    registration_date DATE,
    mileage NUMBER(7) CONSTRAINT mileage_gtz CHECK (mileage > 0),
    price NUMBER(6,2) CONSTRAINT price_gtz CHECK (price > 0) NOT NULL,
    date_in DATE
);

Write INSERT statements to populate a row of the table

INSERT INTO usedCars (car_id, car_make, car_model, registration_date, mileage, price, date_in)
    VALUES ('C0183', 'Vauxhall', 'Corsa', '3-May-1999', 74321, 350, '14-Jun-2014');

There is now a need to add further information to the table. Write an ALTER statement to add a new field named 'fuel_type' and add a constraint to only allow the values 'Petrol', 'Diesel', or 'Electric' to be stored in it.

ALTER TABLE usedCars ADD fuel_type VARCHAR2(8) CONSTRAINT chk_fuel_type CHECK fuel_type IN ('Petrol', 'Diesel', 'Electric');

Write statements to update the date in the new "fuel_type" field so that records with the car_id C0183 and C0334 contain "Petrol".

UPDATE usedCars SET fuel_type ('Petrol') WHERE car_id = 'C0183' OR car_id = 'C0334';

Write a query to calculate and display the average mileage of all cars in the usedCars table.

SELECT AVG(mileage) FROM usedCars;

Write a query to display all cars that have a registration_date with a year of 2002 or later

SELECT * FROM usedCars WHERE registration_date > '01-01-2002';

Describe what is meant by DDL in SQL and give an example of two objects that can be made using a DDL statement
DDL is computer language used to create and modify the structure of database objects in a database
Examples of objects that can be made are:

  • Views
  • Schemas
  • Tables
  • Indexes

DDL - Data Definition Language - changes to structure

CREATE - ALTER - DROP

DML - Data Manipulation Language - changes to database

INSERT - SELECT - DELETE - UPDATE

DCL - Data Control Language - changes to permission / access

A relation is perceived as a table
A row is a tuple
An attribute is a column representing common attributes
A domain is a set of possible values for this attribute
A domain value contains only atomic values. No composite attributes allowed. A pair of values are comparable if and only if they have a common domain.
A relational schema can be formally defined as R = {a1, a2, ... , an}
a = attribute, the number n is the degree of R, the number of tuples in R is its cardinality.

Determination is shown with a right arrow
If we know a student's ID we can determine fname, sName etc.
All attributes except STU_ID are functionally dependent on STU_ID

Superkey - subset of attributes satisfying uniqueness.
Candidate key - superkey satisfying minimality
Primary key - selected candidate key

SQL Wildcards

  • = all

% = any number of characters
_ = specific number of characters

BETWEEN is inclusive
!= <> are both NOT EQUAL

Create an Oracle Database table "abandoned pets" that reports on pets that have been abandoned by their owners and are now being rescued by some organisation. Use "pet_id" as primary key; "pet_name" does not take null values; "age" is below 10 but greater than zero; "pet_owner" last name is not null; pet owner "email" is unique (assume 1 owner for each pet); "date_in" and "breed" should not be null. All data types except "date_in" should be characters.

CREATE TABLE abandonedPets (
    pet_id VARCHAR2(10) PRIMARY KEY NOT NULL,
    pet_name VARCHAR2(25) NOT NULL,
    age VARCHAR2(2) CONSTRAINT age_check CHECK age BETWEEN 1 and 9,
    pet_owner VARCHAR2(25) NOT NULL,
    email VARCHAR2(25 UNIQUE NOT NULL,
    date_in DATE NOT NULL,
    breed VARCHAR2(25) NOT NULL
);

Populate two rows of the table using an INSERT statement

pet_id pet_name age pet_owner email date_in breed
0001 Gotty 5 Fanny [email protected] 26-Feb-13 Terrier
0002 Cribby 3 Maryl [email protected] 20-Mar-13 Hound
INSERT INTO abandonedPets VALUES ('0001', 'Gotty', '5', 'Fanny', '[email protected]', '26-Feb-13', 'Terrier');
INSERT INTO abandonedPets VALUES ('0002', 'Cribby', '3', 'Maryl', '[email protected]', '20-Mar-13', 'Hound');

Relational Algebra

Consider the following information on estate agents. Primary keys are underlined and attributes of the same name in different relations are foreign keys:

Branch (branchNo, street, city, postcode)
Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffno, branchNo)
Client (clientNo, fName, lName, telNo, prefType, maxRent)
Viewing (clientNo, propertyNo, viewDate, comment)

Define the following concepts according to database theory and give one example for each concept using the relations shown above where appropriate

The degree of a relation
The degree of a relation refers to the number of colums on a table The Branch table has 4 degrees (branchNo, street, city, postcode)

The cardinality of a relation
The cardinality of a relation is the number of tuples or rows. No example is possible because the table does not yet have any rows.

The Domain of an attribute
The domain of an attribute is the set of all possible values for one or more attributes. In the staff table, the sex attribute could have a domain of 4 (male, female, other, unknown)

Write relational algebra to list all staff with a salary less than £12,000
σ salary < 12000 (Staff)

Write relational algebra to show cities where there is a branch office or property for rent
Π city (Branch) UNION city (PropertyForRent)

Write relational algebra to show clientNo, propertyNo and comments made by clients. Only include tuples where comments exist
Π clientNo, fName (Client) INTERSECT Π clientNo, propertyNo, comment

Write relational algebra to show clientNo, propertyNo and the city where the property is located
Π clientNo, propertyNo, city (Viewing) INTERSECT Π clientNo, city (Property) >< propertyNo V

Database Concurrency

Explain the term "database transaction" and state the TWO possible outcomes of any transaction and how problems may be resolved, if any occur
Database transaction is a logical unit of work that takes the database from one consistent state to another. The two possible outcomes of a transaction are commit and roll-back

State and explain the FOUR desirable properties of transactions
Atomicity - A transaction is treated as a single "all or none" unit. The entire set of actions must be carried out or none are. Any single failed action means the whole transaction will fail. DBMS must roll back transactions that will not be able to complete successfully.

Consistency - Each transaction, when run, leaves the database is a consistent state. Concurrency control subsystem must ensure this for multiple transactions.

Isolation - Transactions running in parallel appear to be running in isolation

Durability - Effects of committed transactions are permanent, even if the system crashes before actual writes.

Explain the UNCOMMITTED UPDATE (UNCOMMITTED DEPENDENCY) problem and use a diagram to demonstrate this problem occurring between two transactions
Lost Update Problem - is when two transactions update the same action and one update is lost.

  • T1 is withdrawing £10 from an account with an initial balance of £100.
  • T2 is depositing £100 into the same account.
  • Final balance should be £190.
  • Ends up being £90.
T1 T2 Balance
X = balance (read) 100
X = X - 10 100
Y = balance (read) 100
Y = Y + 100 100
Balance = Y (write) 200
Balance = X (write) 90

Apart from the uncommitted update problem, name TWO other types of data integrity and consistency problems encountered in concurrent execution of transactions and briefly explain them
Dirty Reads - Occurs when one transaction can see the intermediate results of another transaction before it has committed. Also called Uncommitted Dependency.

  • Transaction 3 adds £100 to the balance but aborts
  • Transaction 4 deducts £10 from the balance
  • Final balance should be £90
  • Ends up being £190
T3 T4 Balance
X = balance (read) 100
X = X + 100 100
Balance = X (write) 200
Y = balance (read) 200
Y = Y - 10 200
Balance = Y (write) 190
ABORT 190

Unrepeatable Reads - Happens when one transaction reads the same data twice and a different transaction updates it in between reads

  • Transaction 5 checks the balance twice
  • Transaction 6 debits £50
  • T5 should show £100 both times
  • In a dirty read the interfering transaction aborts, in unrepeatable reads it does not. The final value is valid but the original is unrepeatable.
T5 T6 Balance
X = balance (read) 100
Y = balance (read) 100
Y = Y - 50 100
Balance = Y (write) 50
X = balance (read) 50

Incorrect Summary - Occurs when multiple values are being read and some are changed by another transaction midway. Also called Inconsistent Analysis problem.

  • Transaction 7 is summing balances from three accounts (X, Y and Z) with balances of £100, £50 and £25
  • Transaction 8 deducts £10 from Z midway
  • Final total should be £175
  • Ends up being £165
T7 T8 Total
Total = balance (X) (read) 100
tmp = balance (Y) (read) 100
Total = total + tmp 150
Z = balance (Z) (read) 25
Z = Z - 10 25
Balance = Z (write) 15
tmp = balance (Z) (read) 15
Total = total + tmp 165

UML

Explain the meaning of the Generalisation Relationship in UML, showing how it is represented graphically in a UML diagram
Generalisation in UML means inheritance. When we show a generalisation relationship between two actors or two use cases, then we are indicating that the child actor or use case is an instance of the base actor or use case, and something more. In generalisation relationships the arrow points towards the thing on which we are expanding. Employer generalises User. Create Priority Job Listing generalises Create Job Listing.

An element is a specialisation of a more general element.

Generalisation.png

Show how Dependency is represented in UML and explain the purpose of the <<include>> and <<extend>> stereotypes when applied to Dependency in Use Case Diagrams
Include and extend seem somewhat similar but the best way to keep them straight is to remember that "the include relationship is intended for reusing behaviour modelled by another use case, whereas the extend relationship is intended for adding parts to existing use cases as well as for modelling optional system services" (Overgaard and Palmkvist, 2005, p. 79)

Include: Use to reduce duplication and increase reuse

Extend: Use sparingly to show extra information or alternate behaviour that you want to communicate to the reader

Include-extend.png

Explain the purpose of a Use Case Specification and, with appropriate examples, indicate 3 items that should be present in such a specification
The main purpose of a Use Case Specification is to specify any pre-conditions that must be met in order to start the use case, specify any business rules related to the use case steps and specify any post-conditions that will be present after executing the use case.

Items that should be present:

Owner
Actors
Pre-Conditions
Post-Conditions
Primary Path
Alternate Path
(Class Diagram)
(Additional Notes)
Owner
Employer
Pre-Conditions
Employer chooses "Create Job Listing"
The employer must be logged in
Post-Conditions
A new job listing is created in the system
Primary Path
Employer chooses "Create Job Listing"
System verifies that the employer is already logged in
Employer creates the job listing
Employer saves the job listing
Job listing is displayed in the system for other users
Alternate Path
Employer chooses "Create Job Listing"
System checks if the employer is already logged in
If not logged in, the employer is first redirected to the Login page
After successful login, system return to the "Create Job Listing" page
Employer creates the job listing
Employer saves the job listing
Job listing is displayed in the system for other users

Draw a Use Case Diagram to represent the following scenario

VinylRevival is a service which allows customers to borrow vinyl records (LPs) online. A customer must first register with VinylRevival and then pay a monthly subscription fee. Once registered, they can then create a list of the records that they would like to borrow, selected from the VinylRevival catalogue. Each month the customers are sent a number of records from their list (the number of records that they can borrow each month is determined by the subscription package that they have chosen). When they have finished with the records they simply return them using the specially designed self-addressed, pre-paid, stiff envelope. There is no time limit on how long a record can be kept, and therefore no fees. However, customers will not be sent the next record(s) on their list until they have returned the current ones.

When a new customer registers, they must provide their contact details and delivery details, as well as choosing the particular package to which they want to subscribe. They must also choose a username and password for their account. They will then be asked to provide payment details. A registered customer can choose to update their account details, but they are required to log in to their account first. Some customers may also wish to upgrade their package. This is done whilst updating account details.

When browsing the catalogue, customers can add records to their rental list at any time by clicking the "Borrow this Vinyl" button. They will then be required to log in before the record is added to the list.

Vinylrevival.png

Class Diagrams

How to create a class diagram
1. Identify classes

  • Noun phrases (use only as a general guide)
  • Do we store data about it?
  • Does it carry out operations?
  • Check: If it has no attributes or no operations, then is it really a class?

2. Identify associations
3. Identify attributes and operations
4. Note: Take care with Generalisation / Specialisation

Movies
movieId int
movieName String
description String
price
rating int
stars List
genreCode
+AddMovie()
+DeleteMovie()
Genre
genreCode
genreName
+AddGenre()
+RemoveGenre()
MovieCopy
copyId int
condition String
movieId int
+changeCondition()
Members
memberId
memberName
address
+AddMember()
+UpdateMemberDetails()
Rental
rentalId
memberId
copyId
dateOfIssue
dateReturn
+HireMovie()
+ReturnMovie()

Class-diagram.png

Normalisation

A rental company has a number of branches (shops) across the country. Each of these branches has a number of items available for rent and each branch has an address, telephone number and a manager. Each item has a unique ID number and is based at one branch only. Each item has a weekly rental charge. Customers of the rental company have an ID, address and telephone. Bookings are made by customers to rent items of equipment for a fixed period of time.

The rental company wishes to develop a database to contain all relevant information about branches, items, customers and bookings. You have been asked to prepare and normalise the data.

Write down all the data items which will be required in this case
Branch Name
Branch Address
Branch Telephone Number
Branch Manager

Item ID
Item Name
Item Location

Customer ID
Customer Name
Customer Address
Customer Telephone Number

Booking Start Date
Booking Duration
Booking Return Date

Explain the purpose of normalisation
Normalisation has 4 goals:

  • Arranging data into logical groupings, such that each group describes a small part of the whole
  • Minimising the amount of duplicate data stored in a database
  • Organising the data, such that when you modify it you make the change in only one place
  • Building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data in storage

Explain what is meant by the term third normal form (3NF)
The term "Third Normal Form" means:

  • It is in second normal form
  • There is no transitive functional dependency
  • Only foreign key columns should be used to reference another table

Stating any assumptions you have made, normalise this data into third normal form.
Bold - Primary Key
Underline - Foreign Key

Branch ID
Branch Name
Branch Address
Branch Telephone Number
Branch Manager

Item ID
Item Name
Branch ID

Customer ID
Customer Name
Customer Address
Customer Telephone Number

Customer ID
Item ID
Booking Start Date
Booking Duration
Booking Return Date

From your set of 3NF tables, draw an equivalent entity relationship diagram using a standard notation
Normalisation1.png

Online Rugby Union is a game for those who don't like to get hurt when playing rugby. In Manchester the governing body of the sport is the Greater Manchester Online Rugby Union (GMORU). GMORU maintains a number of records regarding players, clubs, and teams. An example of the player record is shown below

GMORU-player-record.png

There are a number of clubs in the region, and each club may have more than one team. A player is registered for a club and can then play for any of its teams. Each player has their playing history recorded on the GMORU player record. Players may commit offences during a match and the match official will report them to GMORU, who may decide to fine them for their misconduct. These offences are recorded permanently on the player record.

Normalise the data shown in the GMORU Player Record to 3NF, showing the interim stages of 1NF and 2NF

Normalisation2.png

From your set of 3NF tables, draw an equivalent entity relationship diagram using a standard notation that includes cardinalities, attributes, and other information.

GMORU-class-diagram.png

Carry our normalisation and create a class diagram from the form
Dodgy-car-hire.png

Normalisation3.png

Normalisation4.png