1 / 25

Week 12

Database Design . Week 12. Forms. Table Many records at a time Some fields do not fit on one screen Column and row formatting only Can be confusing to new users. Form 1 record at a time All fields can fit on 1 screen Any formatting options you set-up

vic
Download Presentation

Week 12

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. 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

More Related