1 / 43

Chapter 6: Databases and Data Warehouses

Chapter 6: Databases and Data Warehouses. Page 219-228 Data Concepts Introduction to MS Access Introduction to Relational Databases. Introduction. To retrieve data or information, data must be stored in an organized manner

rowa
Download Presentation

Chapter 6: Databases and Data Warehouses

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. Chapter 6: Databases and Data Warehouses Page 219-228 Data Concepts Introduction to MS Access Introduction to Relational Databases

  2. Introduction • To retrieve data or information, data must be stored in an organized manner • Meaning data resources must be structured in some logical manner so that • Can be accessed easily • Processed efficiently • Retrieved quickly • Managed effectively

  3. Example: p224 • What is the phone number of customer John Smith? • Who is the customer who rented copy number 4780-3? • What is the phone number of the customer who rented ‘The Ring II’?

  4. Consequences: • We could ONLY answer the above questions because the data was well organized. • Organization starts with good database design. • Database software make it possible to handle such questions or queries.

  5. Data Storage Hierarchy: • Data in storage is organized as a hierarchy: • Bits • Bytes • Fields • Records • Files • Database

  6. Hardware deals with: • Bit: 0 or 1 • Byte: Letter A is represented as: 0100 0001 • Bits and bytes are the building blocks for representing data: whether in processing, storage, or telecommunications

  7. Users deals with: (control by SW) • Field: unit of data consisting of one or more characters (bytes). Example: • Your Name • Your Address • Your Student number

  8. Users perspective (cont.): • Record: is a collection of related fields • Name + Address + Student number • Student Record: • A. B. Buthelezi • J Section • Esikhaweni • 2009012345

  9. Users perspective (cont.): • File: is a collection of related records • The file of all students, described by their Names Addresses and Student numbers • Database: is an organized collection of integrated files: • UZ database will contain files on Student records, Employee records, Payroll data, Financial Records, etc

  10. A key field: is a field that is chosen to uniquely identify a record so that it can be easily retrieved and processed: Key Field

  11. Summary: • Most businesses and organizations rely on information based on data stored in databases • Its critical to access information • Timely • Complete • Accurate • Valid • And relevant

  12. Ms Access • Ms Access is DBMS (database management system) that allows you to • Create databases • Add, change and delete data • Sort, filter and search data in database

  13. Ms Access • Main elements in Ms Access • Tables • Fields and records • Queries • Forms • Reports

  14. Key concepts: • Understand what a db is • Difference between data and information • How a db is organized • Know some uses of large scale db • Airline booking systems • Government departments • Banks • Hospitals

  15. Table • It contains data related to a SINGLE subject • Each field contains ONE ELEMENT of data • Each field data element is associated with an APPROPRIATE data TYPE • Fields have field properties like • Size • Format • Default value, etc. • Primary key

  16. Relationships: • Reason is to minimizing duplication of data • Is constructed between two tables by the use of TWO FIELDS that contain the same data • Have the same field names • Create a link between the tables MATCHING VALUES • 1:1 • 1:M • M:N

  17. 1:1 • Each record in the first table have only ONE match in the second table AND Each record in the second table have only ONE match in the first table

  18. 1:M • Each record in the first table can have more than one matching record in the second table AND Each record in the second table have only ONE match in the first table

  19. M:N • Each record in the first table have MORE than one match in the second table AND Each record in the second table have MORE than one match in the first table

  20. Example using MS Access: • We like to store data of employees and their insurance detail. • Question: Is there an association between the entities ‘employee’ and ‘insurance’? • Question: What info do we need for employee • Question: What info do we need for insurance

  21. Designing: Step 1 • Association: • An employee has many insurances • An insurance is taken by an employee • This is a ONE to MANY relationship between employee and insurance • 1:M

  22. Designing: Step 2 • Determine the characteristics of each entity or ATTRIBUTES • What describe an Employee? • What describe an Insurance?

  23. Designing: Step 3 • Assign data types to each characteristics • Text • Number • Date • Logical • Currency

  24. Designing: Step 4 • Create a data base SCHEMA representing all the information of the previous steps. • See next slide

  25. Relational Model Diagram:

  26. Designing: Step 5 • Represent Step 4 with a MS Visio diagram • A model must be presented graphical because it • Emphasizes understanding • Communication tool

  27. Employee: • Surname • Salary • What about a key field? Staff number • Employee record: • Jones • R100 000 • 101

  28. Insurance: • Insurance Company (Can companies have the same name?) How to identify a company? • Who are they insuring? Employee ID • The amount • Insurance record: • Company code • Company name • Employee ID • Amount

  29. Joining Files/Tables: • Why? • What is the address of AB Buthelezi, who was insured by the company Six Feet Under • Relational DB: • More than one table are used AND each table contains data about one ASPECT • Tables are connected: the UNIQUE field of one table will appear in the other

  30. MS Access: • Create a database: human_resource • SAVE it in the C-Drive • Create Table: employee using the ‘Design View’ Emp_surname as Text Emp_salary as Currency Emp_number as Number • Key Field?????

  31. Enter Data: • Enter data by ‘Double-Clicking’ on table Employee • Use the tab key to move between fields • Data: • Ndlovu, 100000, 101 • Mfeka, 110000, 102 • Ngubane, 120000, 103

  32. Table ‘Insurance’ • Create Table: insurance using the ‘Design View’ Ins_code as Number Ins_name as Text Ins_amount as Currency Emp_number as Number

  33. Forms: • Enter data in table Insurance using a Form! • This is very unique to Access and very POTENT! • 1, six_feet_under, 1000000, 101 • 2, hambakahle, 2500000, 102 • 3, sos, 1500000,103

  34. Form:

  35. Entering Data:

  36. Summary: (Database Schema) • Database: human_resource • Employee(Emp_surname, Text;Emp_salary, Currency;Emp_number, Number) • Insurance (Ins_code, Number; Ins_name, Text;Ins_amount, Currency;Emp_number, Number)

  37. Instance of relational database: • EMPLOYEE

More Related