Mr C Johnston ICT Teacher computechedu.co.uk

G057 - Lecture 04From Entity to Table Mr C Johnston ICT Teacher www.computechedu.co.uk

Session Objectives • Revise terms from last session, • Understand how to construct database tables, • Understand that each attribute needs a data-type, • Know different types of validation rules which can be applied to attributes to ensure that data remains of the correct type, • Know correct methods of documenting tables within a database and its attributes.

Revision of Terms • An entity is a person, place or object • More commonly in English its know as a noun • E.g a student at college could be an entity • Entities have attributes • E.g a student has a name, address, phone number etc. • Each entity needs a unique identifier if it is to be used in a relational database – this is known as a primary key in database land!

Revision of Terms • Entities have relationships with one another • There are three types of relationships • One to One, One to Many, Many to Many • One to Many relationships are the only type allowed - the other two types need resolving • Relationships between entities are shown in an Entity Relationship Diagram (ERD) • This forms one component of the Logical Data Model (LDM)

Constructing Database Tables • A database table is made up of a set of entities • Each database table needs a name • We normally use the name given to the entity • It should be written in capitals • Tables need attributes, which are found during the process of normalisation • Each attribute needs a data type and some form of validation.

Attribute Data Types • Attributes must be one of the following data types: • Text or String, • Numeric, • Date and Time, • Currency, • Boolean or Logical, • AutoNumber. • Data types have different sizes to choose from – you need to pick an appropriate size as well as data type for each attribute, • Some common sizes for each data type are given on the next few slides.

Attribute Data Types • Text or String: • Consists of a sequence of any characters, • Limited to 255 characters, • For longer text requirements you can use a memo data type. • Number: • Byte: 0 to 255 • Integer: -32,768 to 32,768 • Long Integer: 2,147,483,648 to 2,147,483,647 • Single: small decimal numbers (7dp) • Double: medium decimal numbers (15dp) • Decimal: large decimal numbers (28dp)

Attribute Data Types • Date and Time • General Date – 19/06/1994 17:34:23 • Long Date – 19 June 1994 • Medium Date – 19 – JUN - 94 • Short Date – 19/06/1994 • Long Time – 17:34:23 • Medium Time – 05:34 PM • Short Time – 17:34

Attribute Data Types • Currency • Offers more precision for storing prices • Boolean / Logical • True, False • On, Off • Yes, No

Using The Correct Data Types • You must ensure that the correct data type is chosen for an attribute • The larger the maximum value the more memory in the database is taken up • E.g phone numbers don’t need to be calculated with so can be stored as text

The Data Dictionary • The data dictionary (DD) is a table used to document entities, • A data dictionary along with an ERD makes up a logical data model (LDM) • A data dictionary for a student may look like:

Topic Exercise • For each of the scenarios last week identify attributes for each entity and produce a Data Dictionary. • Compile both the ERD and DD into single page LDM for each scenario. • Create a LDM for customers wishing to book a ferry crossing. Each crossing runs serveral times a time and has one ferry assigned to it. A ferry could be responsible for more than one crossing.

Ensuring Data Correctness • Keeping data integral is part of the job which the DBMS plays • There are two methods to do this: • Validation • Ensuring data is within the right context • Access provides lots of methods for this • Verification • Ensuring data is correct • Access doesn’t provide any methods for this

Now() is a function which gives the current system date and time… useful for constructing validation rules, queries, forms and reports in Access. Other useful functions include: Date() which only gives the date and Time() which just gives the time. Methods of Validation • Format check • Usually controlled by the data type and size selected e.g. If [price] field was set as an integer it would not work properly as the prices entered would have to be whole numbers!! • Presence check • Is the field allowed to be left empty or must the user enter something e.g. [surname] - required = yes, [email] – required = no • Range check • What are the range of numerical values allowed to be entered e.g. [price] - >= 0 AND <=1000, [order] >= Now() If customers have to be over 18: [DOB] <= (Date()-6754) 6754 is number of days old you would be if 18. So we take this away from current date to check the DOB is allowed. 6754 is calculated by: 365 days a year * 18 years + 4 extra days for leap years

Constructing input masks can be tricky – L is for mandatory letters (A-Z), ? is for optional letter (A-Z), 0 is for mandatory numbers (0-9), 9 is for optional numbers (0-9). For full details see: http://office.microsoft.com/en-us/access-help/control-data-entry-formats-with-input-masks-HA010096452.aspx Methods of Validation • Picture check / Input mask • Used to check if the data entered looks like a defined pattern of text and numerical characters. Suitable for post codes (can be awkward) and phone numbers e.g. [postCode] – LL0 0LL (allows CV1 5NL but B1 5NL and CV10 5NL not allowed!!) [phoneNumber] – (00000) 000000 (allows 02476 123123 and 07957 123123) • List of valid values • Limit the values to only those on a defined list of from another table e.g. [gender] - “male” or “female”, [carID] – looked up from CAR • Default values • Used to suggest a value for a field which is common or default e.g. [city] – default = coventry [orderDateTime] – default = now()

Validation Text – a friendly message which alters the user if a validation method has been broken and tips on how to solve the error Data Dictionary With Validation • For each field within your data dictionary think about which validation rule could be added – remember to add a validation text column to record any planned validation text.

Topic Exercise • For each of the data dictionaries you have created add some validation rules to each entity.

Mr C Johnston ICT Teacher computechedu.co.uk