Database Management Concepts Ch 17 – syllabus 14.3

We have already covered normalisation at the end of LVI

Please recap these notes

The purpose of a Database Management System (DBMS)

• Storage of data• In separate tables with linked fields • Within the structure of database or data

dictionary.• Provides interface between user and data• And allows data to be queried • And allows construction of reports.• It also controls access to the data/security

How a DBMS can help consolidate data from several different places

• Provides a buffer between the user and the underlying structure of the database so the user is not concerned with the structure of the database, just with making relevant use of the data

• Can be used to create a consistent database with a consistent interface so if users move from one location to another they already know how to use the system

• RDBMS will control access to data so all places will see the most up to date data

• In event of disaster, RDBMS should help with disaster recovery e.g. provide ability to consolidate data and roll back to a known correct state

• RDBMS may provide improved security to the system so that there is less chance of inexperienced users inadvertently changing data or malicious attempts to corrupt data succeeding

The role of a Database Administrator

• Designs the database by setting up appropriate tables, relationships, fields, naming conventions etc.

• Allocation of data access to users including access rights, providing user names

• Maintains the data dictionary i.e. the data structure• Provides training to users as appropriate so new users learn how

to use the system, and existing users get updates as necessary • Monitors the performance of the RDBMS and makes appropriate

adjustments to deal with problems as they arise e.g. system taking too long to produce a report

• In charge of the backup procedure and ensures the database is available to users at the times that they need it

• Notify users of any changes so that they can make changes to the way they use the database appropriately e.g. availability of new reports/ queries

DAMPMIN

Data Consistency

• Data is stored as one value, and not stored again as another value.

• EG if Gayle Robinson’s name is stored in one table, then stored as Gail Robinson in another table this data would be inconsistent.

• Consistency comes with reduced redundancy where data is only stored once, and this is the sole source of that data.

Data Integrity

• Is the correctness of the data or how trustworthy the data is.

• It should be free from corruption and validated whenever it is transferred from one medium to another.

• EG Validation of data such as range check on an amount of currency as it is typed from a data capture form to a RDBMS

• EG Parity check of data as it is sent over a telecommunications link.

Not corrupted

Data Redundancy

• Data is stored as one value in many places• EG Lauren Carpenter’s name and contact

details are stored in the personnel table, and her name and contact details are also stored in the payroll table.

• Redundancy means data is more likely to be inconsistent as any changes to one data item must be made manually to all its occurrences.

• A RDBMS reduces redundancy by creating a unique key field EG PersonID to replace duplicate details in other tables.

Data Independence

• Data tables are separate from the programs/functions that use them

• EG the same table may have two or more different views: the delivery function can see customer name and address only; however the credit control function can see the customer income, credit history, and contact details

• Changes in the structure of the data tables only affects those programs/ functions that are reliant on that part of the structure

• EG the introduction of a new field to a table only needs changes made to the functions that will use the field and not to all the functions that use the table.

Data Normalisation• Process of breaking down complex data structures

into simpler forms • (1NF) Removal of repeating fields • (2NF) Removal of composite keys/partial key

dependencies• (3NF) Removal of non-key dependencies• Normalisation is the process for making the structure

of a relational database more efficient by defining tables, fields, and relationships to minimise duplication of data and increase data consistency. Data integrity is assured by data validation specified in the data dictionary. Complex queries are also achieved using efficient relationships between tables.

DBMS Jargon

• Entity (Table/Record); Person / place / thing about which data is stored e.g. patient

• Attribute (Field); Property associated with the entity e.g. patient’s blood group

• Relationship; Link between entities e.g. patient can have one ward but one ward can have many patients. This is a one to many relationship.

Entity Relationship Diagrams

• Provide a diagrammatic representation of the structure of the data and shows the types of relationships within the database. The relationships include:– one to one (not common) – one to many (common)– many to many (not physically possible) better

to have:

The Nature of a Client/Server Database System (P210)Client Computer: A local workstation that just provides the user interface to request data queries/reports from the server

Server Computer: A remote computer where the processing is carried out to provide the query results/reports to the client computer. The server computer is more powerful than the client computer because it has to contain the whole RDBMS.

The advantages of a client/server database system• cost-effective: clients are cheaper than servers so this expensive resource is made available to a large user base

• data consistency is maintained as one copy of data is held on the server, rather than a copy held on each workstation

• communication between client & server is minimal as only requests/results are communicated, not entire databases

• Department specific report formats or queries can be held on workstations meaning less room is taken up on the server and they are less likely to be accessed by the wrong people.

The EndNow do the exam questions! Do the exam questions!