Unit 18: Database Modelling

1. Unit 18: Database Modelling Session One Part One

2. Aims: Discuss unit content and assessment Discuss what is a database Research who uses a database and why? Discuss purpose and features of a relational database Learn some terminology focused around databases

3. What is covered? Understand the features of relational databases Features: entities; attributes; relationships; benefits Entities: key fields eg primary keys, foreign keys; referential integrity; auto incremented keys; field attributes; data redundancy Attributes: field properties eg data types, size, validation rules Relationships: one-to-many; one-to-one; many-to-many; normalisation Benefits: reduced data redundancy; other eg reduced data storage, faster access, efficient updating, searching, sorting, reporting

4. What is covered? Be able to design, create and populate a relational database Design: relationships; tables; queries; data entry forms; reports; design documentation eg DFds, ERDs, data dictionaries, structured English Creating relationships: normalisation (first, second and third normal forms); modifying; cascading updates; cascading deletes Query design: selection of data types; use of logical operators eg AND, NOR, NOT Data entry forms: verification routines; validation routines eg input masking, checks for completeness, data consistency; data redundancy; visual prompts; dropdown; combo boxes Populate: data entry; importing data from external sources eg other databases, spreadsheets, text files Exporting data: query results; report results; destination eg spreadsheet, database Advanced features: creating styles for fields, tables, forms, reports; creating styles to match user need; consistency eg using tool box; customising: menus and toolbars eg use show/hide functionality, add buttons; automated functions eg macros, scripts, program code Errors: reasons eg different data types, poor design, inconsistent normalisation; rectification

5. What is covered? Be able to test a relational database Testing: test plan; functionality; against user requirements; customer acceptance Evaluation criteria: fit for purpose; justification of features used; suggestions for improvements

6. How will you be assessed? • One Assignment • Practical observations

7. What is a database? • Store information efficiently and reliably so that the information can be retrieved on demand in the form and structure required. • A database is a structured collection of records or data that is stored in a computer system. The structure is achieved by organizing the data according to a database model. • http://en.wikipedia.org/wiki/Database • A collection of information organized in such a way that a computer program can quickly select desired pieces of data. You can think of a database as an electronic filing system. • http://www.webopedia.com/TERM/d/database.html

8. Who uses a database? • When a database holds details about people, it's likely to include their first name, surname and their date of birth. In addition to this, specialist information is stored depending on the database's intended use. • The police have details of all known criminals in a database, e.g. crimes they've committed. • Schools use a database to store details about their pupils, e.g. how many days they've been off school sick. • A hospital will store details of all its patients in a database, e.g. a history of their health issues. • The Government uses a database to store records of people's income tax payments. • A database is used to keep track of all the drivers in central London who have (or haven't) paid the Congestion Charge. • In the case of the Congestion Charge database, if someone hasn't paid the congestion charge, a fine will be issued. The database would play an integral part in automating the process. • Information stored in the other databases listed above could be used in similar ways.

9. Why use a database? • Databases can store very large numbers of records efficiently (they take up little space). • It is very quick and easy to find information. • It is easy to add new data and to edit or delete old data. • Data can be searched easily, eg. 'find all Ford cars‘ • Data can be sorted easily, for example into 'date first registered' order. • Data can be imported into other applications, for example a mail-merge letter to a customer saying that an MOT test is due. • More than one person can access the same database at the same time - multi-access • Security may be better than in paper files. http://www.bbc.co.uk/schools/gcsebitesize/ict/databases/2databasesrev4.shtml

10. Why use a database? • Minimise unnecessary data redundancy (duplications) • Ease of modification • Simple design • Keep data consistent, data is only stored once • Can represent real world entities

11. What is a DBMS? Database Management System • The DBMS is a software provided by the database vendor. • Software products such as Ms Access, Oracle, MS SQL Server; are all DBMS • The DBMS provides all the basic services required to organise and maintain the database, these include: • Moving data to and from data files as needed • Managing data when access is required by multiple users • Managing transactions so that each transaction’s database change are an all- or- nothing unit of work • Support for a query language in order to retrieve data • Provisions for backing up the database and recovering from failures • Security to prevent unauthorised data access and modification

12. How does it work? Database Management System • By using layers of data abstraction databases have the unique capability of presenting multiple users of the data with their own distinct view, while storing the underlying data only once. • Users can then access an application with the purpose of storing and retrieving data. • The Physical Layer – contains the data files, e.g. the tables in access • The Logical Layer – also called the schema. It transforms the data in the data files into a common structure • The External Layer – the user’s views.

13. External Layer View 1 View 2 View 3 Logical Layer Internal Schema Physical Layer Database file 1 Database file 2 Database file 3 Database file 4 Layers of data abstraction

14. Types of database • Using the internet find out about the following types of databases: • Flat file – A flat file database is a database designed around a single table. • Relational database – Incorporates multiple tables that are related together using keys. 10 minutes

15. Common Terms • Using the internet find out what the following terms mean? • Field • Column • Row • Primary Key • Foreign Key • Atomic • Entity • Table • Data redundancy 25 minutes Extra Task: Normalisation

16. Terminology Column Table The data values held are stored in the tables A table has rows and columns. Each column represents a particular data value, e.g. Author. Each row represents a record of a “thing”, e.g. Order Row

17. Terminology Entity An entity is a name of some part of the data chosen so that it can be referenced by a name. In this example the entities are the same as the tables. For example: Customer, Order, Item etc.

18. Terminology Attribute / Field An item of data relating to an entity e.g. FirstName. What are the other attributes in this example?

19. Conclusion: Discussed unit content and assessment Discussed what is a database Researched who uses a database and why? Discussed purpose and features of a relational database Learnt some terminology focused around databases