Lesson video

Hello and welcome to Computing, my name is Mrs. Holborow.

I'm so pleased you've been able to join me for the lesson today.

In today's lesson, we're going to be looking at the features of a database management system, and then we are gonna go on to design our own relational database.

Welcome to today's lesson from the Unit: Databases and SQL.

This lesson is called Database Design, and by the end of today's lesson, you'll be able to design a relational database structure for a given scenario.

Shall we make a start? We will be exploring these keywords throughout today's lesson.

Shall we take a look at them? DBMS, DBMS, DataBase Management System: a software package that allows a database administrator to maintain one or more databases, ERD, ERD, Entity Relationship Diagram: a diagram that shows the relationship between two or more entities.

Entity, entity, something about which data will be stored, for example, a customer or a booking.

Look out for these keywords throughout today's lesson.

Today's lesson is broken down into two parts.

We'll start by explaining features of a DBMS, and then we'll move on to design a relational database.

Let's make a start by explaining features of a DBMS. A DBMS is a database management system, a software application that allows a database administrator to maintain one or more relational databases.

Most DBMS provide a graphical user interface to allow administrators to work with the database.

Typical features include, a set of tools for accessing and maintaining the database, the ability to view the database from different viewpoints, according to the requirements of the user.

The ability to optimise the database, and security and access control to the data.

Time to check your understanding.

Fill in the gaps to complete the sentences.

A DBMS is a: a software application that allows a database administrator to maintain one or more, databases.

Pause the video, whilst you fill in the gaps.

How did you get on? Should we have a look at the answers together? A DBMS is a database management system: a software application that allows a database administrator to maintain one or more relational databases.

A DBMS acts as a middle layer between users and the database files.

It separates the data from the applications that use it, allowing users and programmes to work with the data without needing to know how it is stored.

A set of tools for accessing and maintaining the database.

Table creation, table in a database is structured in tables.

The database management system helps define, create, and connect them.

Form creation, forms make it easy for users to view, add, or change data based on tables or queries.

Query creation, queries let users search for specific data, even across multiple tables.

Report creation, reports summarise and present data in a way that's easy to print, using tables or charts.

The ability to view the database from different viewpoints according to the requirements of the user.

As the DBMS separates the data from the applications that use it, different viewpoints can be set depending on the need of the user or the application.

Different users may also use different applications to access the DBMS. such as a desktop, web, or mobile phone application.

Optimise the database.

A DBMS can help keep data accurate and consistent by: Removing duplicate data, all applications share one database, so there's no need to store the same data in multiple places.

Avoiding inconsistent data, since there's no duplication, the same data can't have different versions.

Checking data input, the DBMS checks can have data validation methods which prevent users from entering erroneous data.

Time to check your understanding.

A college keeps a list of students and their classes in a database table.

What is the issue with the extract of data shown from the database table? Is it, A, data accuracy, B, data redundancy, or C, data inconsistency? Look carefully at the data, and have a think about your answer.

<v ->Did you Select B, data redundancy?</v> Well done.

You can see here because the students take multiple classes, their Student ID, Firstname, Surname, and Date of Birth are repeated multiple times in the database table.

Security and access control to the data.

A DBMS can be used to set permissions, like read or write, for each user, and these permissions may vary depending on the type of user.

The DBMS checks these permissions every time someone tries to access the data, helping to prevent accidental or intentional damage.

Many DBMS also keep track of who uses the database and when.

They record what changes each user makes.

This helps undo mistakes by reversing wrong actions.

Many database management systems are proprietary, which means that they are commercial products.

However, there are also free and open-source products available, for example: MySQL, when you instal it, you get a graphical user interface, which is called, "phpMYAdmin," that allows you to set up and work with one or more databases.

Another example is DB Browser for SQLite, "DB4S, this allows you to work with SQLite databases.

An SQLite database does not require a database server, but is designed to be stored as part of an application.

Okay, we're moving on to our first task of today's lesson.

I'd like you to explain two features of a database management system.

Pause the video here whilst you complete the task.

How did you get on? Did you manage to explain two features of a DBMS? Well done.

Let's have a look at a sample answer together.

So the first feature we've chosen is security and access control.

A DBMS allows you to set different permissions for users, such as read-only or full editing access.

It checks these permissions every time someone tries to access the data, helping to protect it from accidental or intentional changes.

Many DBMS also keep a record of who accessed the database, when they did it, and what changes they made.

This makes it easier to track activity and undo any mistakes.

The second feature we've chosen is: Easy data management and presentation.

A DBMS helps organise data into structured tables and makes it easy to create forms for entering or updating information.

You can also use queries to search for specific data, even across multiple tables.

Additionally, reports can be generated to present the data clearly, either in tables or charts.

Remember, if you'd like to pause your video here to add any detail to your answers, you can do that now.

Okay, we've explained the features of A DBMS, let's now move on to design a relational database.

One of the first steps in database design is to create a conceptual data model, a design.

A data model helps visualise how data is structured for a given scenario, including the relationships between the data elements.

One of the easiest ways to show how data is organised is by using an entity-relationship diagram, an ERD.

An entity is something real, like a person, place, or thing that we want to store information about in a database.

It has attributes, which are the details about it, for example, a student's name or their age.

In a database, an entity becomes a table.

The table has records, which is each person or item, and fields, where each field is a different piece of information, like the person's name or age.

In an ERD, each entity is represented by a rectangle, and relationships between the entities are depicted as lines.

The ends of these lines illustrate the cardinality of the relationship.

Cardinality means how many times one thing in a database is linked to another.

A one cardinality is represented by a straight line at the end of the relationship.

A many cardinality is represented by a splayed line, which is commonly known as a crow's foot.

Here are some examples of those relationships.

So, on the left-hand side, we have a one-to-one relationship with a straight line connecting the rectangles.

In the middle, we have a one-to-many relationship.

So, the connection to the left-hand rectangle is a single line, but the connection to the right-hand rectangle uses the crow's foot, a many relationship.

And then on the right-hand side, we have a many-to-many relationship, where we have the crow's foot going into both rectangles.

Talking about the relationship between two things in a database can help you understand how they are linked.

A useful trick is to make sentences that start with, "Each.

." to the link from both sides.

For example, in a school: Each student has one ID card.

Each ID card belongs to one student.

This shows a one-to-one relationship, one student is linked to one ID card.

In a relational database, implementing a one-to-one relationship is uncommon.

It is usually done for performance reasons or to store sensitive information in a separate table with restricted access.

An example of a one-to-many relationship is the relationship between a teacher and a teaching class: Each teacher teaches many classes, and each class is taught by one teacher.

Note, in this school example, we've assumed that each class can only be taught by one teacher so that the database is not too complex.

In reality, a class might be taught by multiple teachers.

So, one teacher teaches multiple classes.

Time to check your understanding.

What would be an example of a many-to-many relationship in a school? Maybe pause the video whilst you have a think.

Did you think of an example of a many-to-many relationship in a school? Here's an example for you.

An example of a many-to-many relationship is the relationship between a teaching class and a student.

Each class can contain many students, and each student can be enrolled on multiple classes, so this is a many-to-many relationship.

After identifying all of the entities and relationships, you can compile them into an ERD.

So here's an example of our school system.

So, we have the student at the top left, which has a one-to-one relationship to the ID card.

The student has a many-to-many relationship to the class, and the teacher has a one-to-many relationship to the class.

It can be useful to show the attributes associated with each entity in an ERD.

The attributes are displayed with an oval shape and are connected to the associated entity with a line.

Each entity identifier, or primary key is shaded.

So you can see here, for the teacher entity, we've added the attributes, email, teacher_id, first_name, last_name, and date_of_birth.

The teacher_id the entity identifier, or primary key, and we've shaded it light green.

Time to check your understanding.

What attributes and entity identifier, or primary key, could be included for the class entity? Pause the video here whilst you have a think.

How did you get on? Did you manage to identify the attributes for the class? Let's have a look at some examples here.

So we have teacher_id, class_id, description, start_time, end_time, and room_number.

The class_id is the entity identifier, or primary key in this case.

Okay, we're moving on to our final tasks of today's lesson.

I'd like you to create an ERD for a vehicle service centre.

The database will have three entities, vehicle, owner, and service.

What attributes will be needed for each entity? The first one, for vehicle has been done for you.

Pause the video here whilst you complete the task.

How did you get on? Did you manage to identify the attributes? Well done, let's have a look at some examples.

So for owner, we have email, owner_id, first_name, last_name, and phone_number.

You may have some different attributes, but that's absolutely fine.

Here, we've identified that owner_id, will be the primary key for this table.

For service, we have mechanic_id, service_date, registration_number, and mileage, and the service_date is going to be the primary key.

I'd now like you to decide on the relationship between each entity, and create the completed ERD to show each entity and its relationship to the other entities.

Pause the video whilst you complete the task.

How did you get on? Did you manage to complete your ERD diagram? Well done.

Here's an example.

So, we have owner and vehicle.

One owner has a many relationship to the vehicle, and then we have service, so one vehicle has a one-to-many relationship to the service.

So, one owner can have more than one vehicle, and one vehicle will have more than one service.

Okay, we've come to the end of today's lesson, Database design, and you've done a fantastic job, so well done.

Let's summarise what we've learned together in this lesson.

A-DBMS is a database management system: a software application that allows a database administrator to maintain one or more relational databases.

A data model helps visualise how data is structured for a given scenario, including the relationships between data elements.

One of the easiest ways to show how data is organised is by using an entity relationship diagram, or an ERD.

I hope you've enjoyed today's lesson, and I hope you'll join me again soon, bye!.