week 2

Learning Aims and Objectives:
Aim: In this week's page, students will learn the.

Objectives:
1. By the end of this week's page students will be able to.

2. By the end of the week's page students will be able to.

3. By the end of the week's page students will be able to .

4. By the end of the week's page students will be able to

5. By the end of the week's page students will be able to.

A The purpose and structure of relational database management systems

A1 Relational database management systems

• Types of relational database management systems (RDBMS) and their characteristics.

What is a RDMS?

A (RDMS) Relational database management systems is a database that is used to keep data in tables (this is also refered to as an entity or relation), each of these tables holds linked data, for example client details. The relational data structures join the tables into datasets

RDMS can be:

Desktop, for example MS Access®

Client-server, for example Oracle®

Open source, for example MySQL

• RDBMS based on relational models:

o relational data structures – relation, attribute, domain, tuple, cardinality and relational database

o relational algebra sets – symbols, union, intersect, join, select

ᴗ (union) combines two tables with matching fields for all the records, removing any duplicates:

Products_1 ᴗ Products_2

Creates a dataset of all the records from both tables

ᴖ (intersect) creates a dataset where records match:

Products_1 ᴖ Products_2

Creates a dataset of matched records from these tables

Can you suggest more examples of these relationships?

⋈ (join) creates a dataset from two or more tables:

Staff ⋈ Department

Joins the Staff and Department tables into a dataset

σ (select) for a subset of records matching criteria:

σ Genre = “tablet”(Products)

Selects records with tablet in Genre field of Products

Can you suggest more examples of these relationships?

o database relations – entity relationship, generic, semantic

o relational keys –

Super key - As an example, a table could contain columns like those below

Customer name
Customer ID
National Insurance Number (NI)
Address
Date of birth

A set of columns could be used, Examples of superkeys are as follows:

Name+NI+Birthdate
ID+Name+NI

The process may be reduced. You can assume that the customer ID is unique to each customer. So the superkey could be reduced to just one field the customer ID, which is the candidate key. However, to ensure absolute uniqueness, a composite candidate key may be formed by combining customer ID with NI.

candidate key,

primary key: - This type of key is used as a unique identifer for a given data set, this can be a combination of letters and numbers. You as an individual in the UK will at the age of 15-16 be sent your National Insurance Number, this number stays with you up to and beyond your death. The number is used to allow things like tax and national insurance to be collected against you, but to also enable you to use this when it comes to claiming benefits, state pensions, and health benefits. This number needs to be unique to you. In databases, we use (in most places) an auto number to provide this detail. Within Access, the auto number starts at 1 and increments by one. If a number is deleted from the database, for example, a mis-entry the number will not be able to be used again.

foreign key : - This key is used often in databases as it is essentially the primary key again, however, in this instance the key is in another table. For example, if you took your national insurance number and put it into the table, employment ( this is a table that would have your employment information) your id could appear more than once as you may be employed by different employers over the years. Within the "Employment" table, it would have its own primary key so that your foreign keys could appear more than once.

Integrity constraints

entity integrity,

referential integrity

Entity relationships

one-to-one,

one-to-many : - Found in almost all database, one-to-many relationships are the link between a data set to another relevant dataset, for example, you are a student at the college, you have a student ID, this id is unique to you. You are studying a course that has a Course ID. You are not the only one studying on this course there are others. So we would have a table that records the enrolment. So your unique id would be put into the enrolment table and next to it would be the course id too.

many-to-many.

A2 Manipulating data structures and data in relational databases

Use of RDBMS software tools and structured query language (SQL) for defining,

modifying and removing data structures and data:

updating, inserting, deletion

When using SQL we can use a series of commands to enter data in to tables, the below are examples of SQL commands used;

INSERT INTO Table_Name (FIELD1, FIELD2) VALUES ("Hello","World");

https://www.w3schools.com/sql/exercise.asp?filename=exercise_insert1

UPDATE Table_Name SET (FIELD1= "Hello" FIELD2="World") WHERE Condition;

https://www.w3schools.com/sql/exercise.asp?filename=exercise_update1

DELETE FROM Table_Name WHERE condition;

https://www.w3schools.com/sql/exercise.asp?filename=exercise_delete1

Retrieval of data for queries, reports

You will often use the below structure to recall data and information from a system to enable the creation of a report;

SELECT * FROM Table_Name WHERE Condition;

the above statement can be shortened to

SELECT * FROM Table_Name

This statement will recall all information from the identified table and display it to the user, whereas the previous statement can enable the user to define specific search criteria, for example looking for only females.

https://www.w3schools.com/sql/exercise.asp?filename=exercise_select1

• administration of users

• security, integrity, recovery.

Last Updated
2024-09-04 14:23:40

Links to Learning Outcomes		Links to Assessment criteria
AO1 Demonstrate knowledge of database development terminology, standards, concepts and processes

English

Reading: Read and understand texts, selecting material appropriate to purpose, collating from different sources and making comparisons and cross-references as appropriate.

Sentence Structure: Use a range of sentence structures for clarity, purpose and effect, with accurate punctuation and spelling.

Maths

Analysis of information: Interpreting Results, Drawing conclusions from data, Comparing data

Using Numbers: Counting, Place value, adding and subtracting, multiplying and dividing.

Collect and record data: Questionnaires, Observation, Tally

How 2's Coverage

Anonymous Assessment - Learners assess an anonymous piece of work containing deliberate mistakes against given success criteria.

Product Evidence - Learners to create product evidence to demonstrate understanding

Question and Answer - Verbal discussion with learners to quantify understanding