Week 12

1. Database Design Week 12

2. Forms

3. Table Manyrecords at a time Somefields do not fit on one screen Column and rowformatting only Can be confusing to new users Form 1recordat a time Allfields can fit on 1screen Anyformatting optionsyou set-up “User-friendly”and less-intimidating than tables Table Vs. Form

4. WHAT IS A FORM?• “Electronic Equivalent” to a paper form• allows you to create a custom layoutfor your data• more “visually appealing” for “Data Entry”• when you need to control which datais displayed or entered

5. Why Use Forms? Provide a “User-Oriented” interfaceto the data Only asks for specific information Ability to use “non technical” terms Should be designed around individual tasks

6. Fields in a Form The field labelsin a form are like the labels on an application. The field text boxes are where you supply the data, like the blank lines on an application.

7. FORMS cont. • Forms are made up of controls, such as text boxes, buttons, document tabs, and drop-down lists, grouped in a way that makes them easy to use • The controls in the form are usually “bound”, or “linked”to the tables or queries in your database — but not always. • For example, a control that displays “Customer data” is probably bound to either the “Customer” table, or to a query that retrieves Customer data. • BUT: a form control that displays your corporate logo isn't bound to a table field; it just points to the image it displays.

8. CLICK HERE TO OPEN “NORTHWINDS DATABASE. Accdb”

9. REPORTS • Ways of generating printed output • Can create reports that show totals & grand totals of value in a particular field • Reports give you the ability to present components of your database in an easy-to-read, printable format. Access 2010 lets you create reports from both tables and queries.

10. Business Narrative • Vital link between database designer and the end users • Purpose is to make clear the “data requirements” • The story, business rules, purposes of database; what kind of info it will hold • Will have to go over the DESIGN multiple times/days to make sure the DESIGN is correct • Get other people/users to comment on design • Example: • “Algonquin college employs many instructors to deliver various programs comprised of many different courses. “

11. Starting an ERD programs instructors courses • Identify the “entities” • Look at NOUNSof the “Business Narrative” “Algonquin college employs many instructorsto deliver various programs comprised of many different courses.”

12. Entities and Their Attributes • “Entities” represent something of importance • “Entities” are made up of several “Attributes” (the columns), which describe the tables:

13. Review Tables / Entities • Tables (aka Entities) in the relational model are used to represent “things” in the real world. • Entities are made up of attributes • Attributes(columns/fields) are pieces of data that describe the entity • Rows are called records

14. What is a Primary Key? • The PRIMARY KEY of a table is the column whose values are different in every row • Should be 1st field • A field in the database that guarantees UNIQUENESS • Speeds “data retrieval” • RULE: Each record in a table must have a “PRIMARY KEY”. • In MS ACCESS a “Primary Key Field” is represented by a tiny gold key to the left of the Field Name

15. Primary Keys • Some entities have “ready-made” primary keys, for example: • Catalog codes • If an entity lacks a reliably unique field • Use a “system-assigned”: “AUTONUMBER”

16. What is a Foreign Key? • A field in a table that is the Primary Key of another TABLE. • A foreign key is used to indicate a relationship between 2 or more tables. • NOT every table has to have a Foreign key • Always a “NUMBER” type, linked to an “AUTONUMBER” type in another TABLE • Whenever you see: “MANY”—this will be the FOREIGN KEY

17. Three Basic Types of Relationships CARDINALITY: • One to One (1:1) • One to Many (1:M) • Many to Many (M:M) • The 1 is the “PARENT”, and the M is the “CHILD” • Called the CARDINALITY of the relationship • The cardinality indicates the MAXIMUM number of RELATIONSHIPS between the entities, • Whenever you see: “MANY”—this will be the FOREIGN KEY

18. One to One Relationships • 1:1 relationships exists when a single record in one table has 1 and only 1corresponding record in anothertable, and vice versa • VERY RARE • When you created a table for something, when it really should only have been a “field” • The 1should really have been an “attribute” of the other (table/entity) an attributeIMPROPERLY DEFINED as an entity • i.e. 1customer,1address • “Address” should have been the attribute of the “Customer” table

19. One to One Example Voter Ballot A voter can cast only one vote in an election. A ballot paper can belong to only one voter. So there will be a 1:1relationship between a Voter and a Ballot Paper.

20. One to Many Relationships • MOST COMMON • 1:M or M:1 • Relationship exists when a record can relate to 1OR MORE records in a 2nd table but.. • A record in the 2nd table can ONLY relate to 1record in the 1sttable

21. One to Many Example Owner Vehicle A person can own more than one car. A car can only have one owner.

22. Many to Many Relationships • M:M • Relationship exists when 1record in eithertable can relate to more than 1record in the othertable • These should be eliminated • 2 related “PARENT”tables, but their relationship is NOT “direct” • Instead, it needs to be related through an additional “CHILD” table

23. Many to Many Example Student Professor • A student can have more than one professor; the same professor can have many students • CLICK HERE TOWATCH A VIDEO EXPLAINING THIS

24. Resolving Many to Many Student Stud_Prof Professor • M:M is not allowed, as an attribute could have more than 1value: • A “Multi-Valued Attribute” • Difficult to generate reports, or do searches • Would have to repeat attributesmultiple times • Instead, we create a “JUNCTION” or “JOINER ENTITY” • The name of the joiner entity is often a COMBINATION of the 2 entities

25. Hybrid / Homework View “14 Week Schedule” > Week 12> 3rdColumn