Database Design IST 7-10

1. Database Design IST 7-10 Presented by Miss Egan and Miss Richards

2. Database Design 5.2.1 Describes and applies problem solving processes when creating solutions 5.2.2 Designs, produces and evaluates appropriate solutions to a range of challenging problems 5.2.3 Critically analyses decision-making processes in a range of information and software solutions

3. Database development Purpose of a database Components of a database Inputs of a database Outputs of a database Data types required to solve a problem

4. Purpose of a database •Database is a collection of data that is organized in a way that allows access, retrieval and the use of data by us users. Characteristics of a database •It is in a digital form •Can be of many sizes •Collection of unprocessed data •Databases allow you to store large amounts of information; search/sort data, organise/present •Relational databases •The DBMS allows you to decide how to display information( access, filmmaker)

5. Components of a database Queries: Ask questions about the data. Purpose is to retrieve specific information from tables; it makes you view them in a single datasheet. Reports: Way of summarising and presenting data. Using a specific area eg. what suburbs customers are in Tables: Where the actual data is presented. It is displayed in (records) rows and (fields) columns. Tables include information on people and stock. Forms: Use to enter or retrieve any data and also edit. Example is a customer form which you just work with customer data. Queries: Ask questions about the data. Purpose is to retrieve specific information from tables; it makes you view them in a single datasheet. Reports: Way of summarising and presenting data. Using a specific area eg. what suburbs customers are in Tables: Where the actual data is presented. It is displayed in (records) rows and (fields) columns. Tables include information on people and stock. Forms: Use to enter or retrieve any data and also edit. Example is a customer form which you just work with customer data. Queries: Ask questions about the data. Purpose is to retrieve specific information from tables; it makes you view them in a single datasheet. Reports: Way of summarising and presenting data. Using a specific area eg. what suburbs customers are in Tables: Where the actual data is presented. It is displayed in (records) rows and (fields) columns. Tables include information on people and stock. Forms: Use to enter or retrieve any data and also edit. Example is a customer form which you just work with customer data. Queries: Ask questions about the data. Purpose is to retrieve specific information from tables; it makes you view them in a single datasheet. Reports: Way of summarising and presenting data. Using a specific area eg. what suburbs customers are in Tables: Where the actual data is presented. It is displayed in (records) rows and (fields) columns. Tables include information on people and stock. Forms: Use to enter or retrieve any data and also edit. Example is a customer form which you just work with customer data.

6. Inputs of a data system Data is organised in layers, known as a hierarchy • Character- is a bit, smallest unit of data the computer can process, it comprises a byte, which represents numbers, letter and symbols. • Fields- combination of related characters (bytes) and is smallest unit of data we can access. A FIELD NAME identifies each field, when searching for a specific field you type in field name. Field size defines the maximum number of characters a field can contain( 6 and 6) • Records-is a group of related fields, e.g. student record includes a set of fields about that one student! PRIMARY KEY identifies each record in a file. • Files- Known as a DATA FILE, collection of records stored on a storage medium such as a hard disc.

7. Outputs of a database Reports- As stated above, reports are a way or presenting and summarizing specific data. Examples are invoices, mailing labels and telephone lists that would be presented in a table Forms-Provides areas for entering or modifying data in a database. An example of a form for a specific reason is one containing specifics, like an address form/ subhurb form. When it comes to forms, you can make them specific colour, shading, text, boxes, graphics, fonts and also ways of formatting. It normally displays data for every field in a single record Information- The data that is presented in the reports and forms Reports- As stated above, reports are a way or presenting and summarizing specific data. Examples are invoices, mailing labels and telephone lists that would be presented in a table Forms-Provides areas for entering or modifying data in a database. An example of a form for a specific reason is one containing specifics, like an address form/ subhurb form. When it comes to forms, you can make them specific colour, shading, text, boxes, graphics, fonts and also ways of formatting. It normally displays data for every field in a single record Information- The data that is presented in the reports and forms Reports- As stated above, reports are a way or presenting and summarizing specific data. Examples are invoices, mailing labels and telephone lists that would be presented in a table Forms-Provides areas for entering or modifying data in a database. An example of a form for a specific reason is one containing specifics, like an address form/ suburb form. When it comes to forms, you can make them specific colour, shading, text, boxes, graphics, fonts and also ways of formatting. It normally displays data for every field in a single record Information- The data that is presented in the reports and forms

8. Data Dictionary Data dictionary is a description of each field in a database. It includes field name, data type and description Description is just describing what it is very briefly Field Name is name of field, very short Data type is text memo, number, currency, date/time and yes or no

9. DBMS Data Dictionary DBMS Data Dictionary A description of every field in the database and the rules that decide if an entered data value can be accepted It includes The names of every field The type of data the field contains How many characters of space the field needs Any other features

10. Data types required to solve a problem Most important decision in developing a data base is the number of fields each record will have, and the data types they will store • Text • Number • Yes/No • Date • Currency Most important decision in developing a data base is the number of fields each record will have, and the data types they will store • Text • Number • Yes/No • Date • Currency Most important decision in developing a data base is the number of fields each record will have, and the data types they will store • Text • Number • Yes/No • Date • Currency Most important decision in developing a data base is the number of fields each record will have, and the data types they will store • Text • Number • Yes/No • Date • Currency Most important decision in developing a data base is the number of fields each record will have, and the data types they will store • Text • Number • Yes/No • Date • Currency Most important decision in developing a data base is the number of fields each record will have, and the data types they will store • Text • Number • Yes/No • Date • Currency Most suited to answers with numbers, or calculated formula’s Example fields are height, age, mark Closed questions Example fields, gold class member, payment made Allows records to be sorted into calender order Example fields, DOB, membership date Can be used to store any keyboard characters example fields are name, address, gender ($) usually with two decimals places Example fields, amount owing, membership fee

11. Collecting, organising and storing data Sources of data to solve a problem Database storage on a storage medium considering file size, portability and updatability Validation and verification checks of data

12. Sources of data to solve a problem • Information should be: • Accurate • Verifiable: photo, pin number • Timely: up to date • Organized • Accessible • Useful • Cost effective Primary Obtained through: • Interviews • Survey • Census Secondary Obtained through: • Books • Statistics • Newspaper • Media • You must acknowledge your sources • It gives credit to the people who provided the data • Allows others to check its accuracy

13. Database storage Once data is changed in the database it is automatically saved. All data including: records, designs, tables, dictionary, reports, forms and objects are all saved under the one file The addition of data will cause the file size to grow.

14. Validation and Verification checks of data Validation is the process of checking entered data for errors Examples: • Wrong type of data • Data values are too big or small Verification is the process of checking the entered data for accuracy Examples: • Typing and spelling mistakes • Incorrect values

15. Methods of processing and analysing data Editing, searching, sorting records Mathematical calculations

16. Editing, Searching and Sorting records Editing The process of changing data You can insert data in form or report by placing cursor in the desired location With deleting, similar way as the courser goes on section and you press delete You can move data with simply cut, copying and pasting • Sorting • Allows to arrange in particular order • Arrange data in ascending or descending order • Data can be sort alphabetically • Searching • Is the process of locating data and information • You use the find or search commands • You can construct searches through queries

17. Mathematical Calculations There are all different types of calculations you can perform in a database An example is calculate the average values in one field or by multiplying the values in two fields When typing in calculation you must start with a “=” Example is =Avg([Salary]) Simple calculation by adding two amounts together:

18. Macros Are used to perform repetitive tasks in databases • Examples are opening a form or printing reports Macros save time and allow for less errors on databases

19. Methods of presenting information Presentation of reports: header, body text, footer Report layouts Design features on forms and reports

20. Presentation of reports Reports are designed for presenting printed data. They are NOT interactive Reports often display the results of queries When a report is made you can view and edit in the design, report or layout view The report is formatted and includes the header, body and footer Body/text Header Footer

21. The layout view You can further alter the layout of a report in the “ layout view” This allows you to change the size of the report and add and edit. Also allows you to pick themes You can change fonts Pick from a big range of colours to change the header, and also the colour of writing. Add attachments such as images

22. Design features on forms and reports Forms are a common way of presenting the data from an individual record. • Designed for screen displays • Edit and enter data • Display the results of queries

23. Intergration Importing from existing electronic data

24. Importing from existing electronic data Importing graphics Select the report area where the graphic will be inserted Open the insert menu and select picture Locate and open the graphic file from the drive or device where it is stored Data can be • Exchanged between databases and spreadsheets • Exported from databases to word process documents • Imported from data entry forms

25. Exporting data for other uses Data from a DBMS can be exported to spreadsheets, word processed documents and web pages. An example of exporting data is customers records may need to be word processed. Select the export file and destination Access file will now open in spreadsheet Prepare the results to be exported to a spreadsheet Open the file menu and select EXPORT

26. Project Development Processes and techniques

27. Activity 1 http://www.jeopardylabs.com/play/enter-title73452

28. Activity 2

29. Who are we?

30. Who am I? I ask questions about data My purpose is to retrieve specific information from tables I am a single data sheet separate too a table

31. I am A query

32. Who am I? I summarize the information that is in tables I cover single topics example: where customers are from I am a result of a query I am printable

33. I am A report

34. Who am I? You enter and retrieve data from me I display data on a screen and you can collect data and print me out I present information found in a record

35. I am A form

36. Who am I? I am where the actual data is presented I contain rows and columns You are all sitting infront of me

37. I am A table