1 / 49

DATABASES

DATABASES. OBJECTIVES. You should be able to: Define the terms related to a database Identify situations where a database can be used Compare a manual and computerized database State the difference between a flat file and a relational db Identify different datatypes Identify a primary key.

binah
Download Presentation

DATABASES

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATABASES

  2. OBJECTIVES • You should be able to: • Define the terms related to a database • Identify situations where a database can be used • Compare a manual and computerized database • State the difference between a flat file and a relational db • Identify different datatypes • Identify a primary key

  3. TERMS • DATABASE • RECORD • TABLE • FIELD • FLAT (DB) FILE • RELATIONAL DATABASE • DATA TYPE • PRIMARY KEY • RELATIONSHIP

  4. What is a database? A database is an organized collection of data. You can have a manual database or a computerized database. A computerized database is organized into files called tables. These tables provide a systematic way of accessing, managing, and updating data.

  5. ACTIVITY 1 We would like to create a form to hold students’ personal information. We want to store: • Surname • Firstname • DOB • Address • Relgion

  6. Create a form to hold the information. • How would you store the information for 10 students? • Say we wanted to store Students’ Performance info. and Teachers’ information- how would we do that? • What are some of the drawbacks of using this manual database?

  7. Activity 2 • Create a table to store the 10 students information.

  8. TABLE data fields records

  9. 1. Identify the type of data (datatype) that will be stored in each field 2. How would you uniquely identify each student?

  10. DATATYPES

  11. Primary Key

  12. Setting a Primary Key • A primary key is a selected field that uniquelyidentifies a record. • A primary key: - The field can’t have a null value and the value must be unique • Speeds up data retrieval • Sorts records • Enable you to establish relationships between tables

  13. AFL • Put a tick by the fields that can be used as a primary key: • Name • Employee ID • Date of Birth • Height • Personal Identification Number • Phone Number • Order Number

  14. QUESTIONS How many fields are in the table? How many records are in the table? What is the possible primary key? Why? State the datatype of each field.

  15. FLAT FILE vs RELATIONAL DB • A flat file database is a database designed around a single table of data. The flat file design puts all database information in one table with fields to represent all parameters. A flat file may contain many fields, often, with duplicate data that are prone to data corruption. • A relational database is one that contains multiple tables of data that relate to each other through special key fields. The data can be collated, merged and displayed in database forms.

  16. FLAT FILE vs RELATIONAL DB: SCENARIO • To understand the advantages of a relational database, imagine the needs of two small companies that take customer orders for their products. • Company A uses a flat file database with a single table named orders to record orders they receive. • Company B uses a relational database with two tables: Orders and Customers.

  17. FLAT FILE vs RELATIONAL DB • When a customer places an order with Company A, a new record (or row) in the table orders is created. Because Company A has only one table of data, all the information pertaining to that order must be put into a single record. • This means that the customer's general information, such as name and address, is stored in the same record as the order information, such as product description, quantity, and price. If customers place more than one order, their general information will need to be re-entered and thus duplicated for each order they place.

  18. FLAT FILE vs RELATIONAL DB Whenever there is duplicate data, as in the case above, many inconsistencies may arise when users try to query the database. Additionally, a customer's change of address would require the database manager to find all records in orders that the customer placed, and change the address data for each one.

  19. FLAT FILE vs RELATIONAL DB • Company B is much better off with its relational database. Each of its customers has one and only one record of general information stored in the table customers. • Each customer's record is identified by a unique customer code which will serve as the relational key. • When a customer orders from Company B, the record in Orders need contain only a reference to the customer's code, because all of the customer's general information is already stored in customers.

  20. FLAT FILE vs RELATIONAL DB This approach to entering data solves the problems of duplicate data and making changes to customer information. The database manager need change only one record in customers if someone changes addresses.

  21. Which is the flat file and Which is a relational database? CUSTOMER ORDER

  22. RELATIONSHIPS Referring to the previous slide (and the relational database with the tables CUSTOMER and ORDERS) which field will you use to create a relationship between the two tables? Give a reason for your answer.

  23. OBJECTIVES • At the end of this session you should be able to: • Create a table in MS Access 2007 • Create fields and set datatypes • Set a primary key • Use the Properties option • Populate a table with data

  24. Objects in MS Access 2007 • TABLES • FORMS • QUERIES • REPORT

  25. TABLES FORMS REPORTS QUERY A table in a DB contains data on a specific entity A form is a graphical representation of a table A report organizes information from tables & queries and formats the data in a form suitable for viewing A query selects records from one or more tables based on a specific criteria

  26. ACTIVITY • Do Activity 1- Ch. 12, Page 242 (Blue Text/Ch. 14, Pg 312: Log On to IT for CSEC

  27. What are the two Views in creating your table? • Which View allows you to set your fields? • Which View allows you to enter data? The Two Views are: Design View and Datasheet View! Design View allows you to set your fields and their properties and the Datasheet View allows you to populate your table.

  28. Refer to Pg 308- Log On to IT for CSEC for a description of each Field Property PROPERTIES

  29. What are some of the field Properties in MS Access?

  30. CLASS ACTIVITY- 30 MINS • Create the databse Quilt Incorp. (Ch. 14, Page 313: Purple text/Ch. 12, Pg. 243- Blue text:- Log On to IT for CSEC) • Enter the records in the table created in the database Quilt Incorp. (Activity 4, Q.3a- Ch. 14, Page 326: Purple text/Ch. 12, Pg. 256- Blue text:- Log On to IT for CSEC) • If Q3(a) completed do Q3(b),(c)

  31. Extra Task:State what the Field properties Mean: • Field Size • Format • Input Mask • Caption • Validation Rule • Validation text

  32. SUMMARY OBJECTS: Tables, Forms, Queries, Reports Relational Database TABLES RECORDS • A unique field (Primary Key) • Field Definition: Data type & Field Properties FIELDS DATA

  33. AFL: MATCH ME IF U CAN • COLLECT THE STARS BY MATCHING THE DESCRIPTION WITH THE TERM • Time- 5minutes

  34. HOME-WORK • Review Field Properties (Page 308-310: Purple text/Pg. 238-240: Blue text). Do questions on Slide 31 • Define the terms Tables, Form, Query, Report • Go to http://www.teach-ict.net/software/access_2007/access_2007.htm for tutorial on Databases • Go to Schoolrack and view the Power Point on Problem Solving. Prepare the solutions for the Logic Puzzles for next day (Birthday Guessing Game and Brain bash). Try playing the other games online (3D logic and Sliding Block)

  35. RELATIONSHIPS- OBJECTIVES At the end of this session you should be able to: • State, identify and create the different types of relationships created between tables in a database • Understand the purpose of the different keys: primary, foreign, composite

  36. RELATIONSHIPS • One to One • One to Many • Many to Many

  37. Relationships • One of the major advantages of databases such as Microsoft Access is their ability to maintain relationships between different data tables. • The power of a database makes it possible to correlate data in many ways and ensure the consistency (or referential integrity) of this data from table to table.

  38. EXAMPLE • ONE TO ONE — A one-to-one relationship exists between a student and a student number. 2. ONE-TO-MANY — A one-to-many relationship exists between courses and sections: each course may consist of many sections, but each section is associated with exactly one course. 3. MANY-TO-MANY — A many-to-many relationship exists between students and courses: each student can take many courses and each course can contain many students.

  39. Activity: Determine the type of Relationship • A video club member can borrow several movies • Movies and Actors • A Video Club Member can borrow one movie at a time • Students and Teachers

  40. KEYS • PRIMARY • FOREIGN • COMPOSITE

  41. PRIMARY KEY • The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique. Primary keys may consist of a single attribute or multiple attributes in combination.

  42. FOREIGN KEYS • A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables. • This key is used to create relationships between tables

  43. COMPOSITE KEY • A Primary Key uniquely identifies each record/row in a table as said before. It is not always a single-column key, it could be: • a single-column key • or a composite key • A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.

  44. For example, if you have a Student table and a Course table, and one student can select many courses and one course can be selected by many students, so this is a many-to-many relationship. So you need to create the third table to define the relationship, say it's called StudentCourse. It is important to note that you only need the StudentID and CourseID in this table as a composite key. • You do not need an extra identity ID column in this table to uniquely identifies each row because only having an ID column to uniquely identifies each row is not sufficient.

  45. TEACHER’S DEMO • Let’s see how to create a relationship between two tables: BOOKS.accdb

  46. CLASS ACTIVITY • WORK IN PAIRS TO COMPLETE THE PRACTICAL WORKSHEET

  47. REFERENCE: YouTube videos on Relationships • http://www.youtube.com/watch?v=IarzvwTijwk • http://www.youtube.com/watch?v=7XstSSyG8fw • http://www.youtube.com/watch?v=4e6Y003KQdo&feature=related • http://www.youtube.com/watch?v=DpIcvzdm6JI&feature=related

  48. SUMMARY • RELATIONSHIPS: - ONE TO ONE • ONE TO MANY • MANY TO MANY • KEYS: • PRIMARY KEY • FOREIGN KEY • COMPOSITE KEY • REFERENTIAL INTEGRITY

  49. HOME-WORK • Read Ch. 14, Pg. 317-319 (Log On to IT for CSEC) • Explain how the primary key, foreign key and composite key are related to each other. • Why would you enforce referential integrity in a database? • What are the THREE main types of relationships that can be created in a database?

More Related