Understanding Database Management Systems

Databases LO: To describe how a DBMS allows seperation of data from applications and why this is desirable. To describe the principle features of DBMS and how they can be used to create customised data handling applications.

Starter: - What is a relational database? - Attributes are also known as? - Rows are equivalent to? - What is a primary key? - What is a foreign key?

Entity Relations Entity the data we are recording in the table It is important to keep different entities separate. Tables are linked together so that data duplication is kept to a minimum. To avoid data redundancy it is best to have tables that are related in one to many ways. E.g. One customer may have many cars but that car will only have one owner This is called an entity diagram. Customer Car

Case study a hotel booking system How could a database keep track of room bookings using a database application? It shows that each room can be booked many times and each client can make many bookings but each booking is just for one room and Clients contact the hotel with their requirements Rooms will be stored in a database Bookings of these rooms made by clients will be stored in the database. What would the entity diagram look like? Room Client Booking

Translates into. tblRoom tblClient room_number client_ref tblBooking room_type fore_name ID smoking last_name num_nights view phone charge bath email date room_num Primary Key client-ref

Database Design When designing a database you have to make decisions about each field Data Type The software needs to know how to handle the data. Field Size There is no point in reserving more bytes for each field than are necessary Validation Rules can be set up to filter some types of mistake Key Field Choose at least one field to make a primary key.

Fill in the gaps

Sharing Data A large organisation is likely to have a store of data that needs to be seen and worked on many people. The importance of a corporate store of data has led to the development of databases which are used by a whole enterprise. Therefore database software needs to be able to do far more than just read, write and change data it must also be able to: Supply users with no more than they need to work with Protect data from damage and unintended deletion Make sure that as far as possible the data is accurate and up to date Protect data from unauthorised access Make sure all who need access it can, whenever they need it

The DBMS

Key Facts It is a general-purpose tool that allows database administrators to: Create databases applications Protect data Run queries to extract data Keep data consistent Keep data accurate

Seperation of data and applications Application 1 Application 2 Application 3 DBMS Database

Seperation of data and applications It is important to separate the applications from the data so that: Programmers do not have to worry that their applications might damage existing data structures New applications can be written without restructuring the data Data can be more easily shared between applications Data remains consistent because there is just one copy for all applications.

Check your understanding.