DATABASE DESIGN - Algonquin College

DATABASE DESIGN - Algonquin College

DATABASE DESIGN ERD Entity Relational Diagram (ERD) Visual display of tables and their relationships for a database Used for Relational Modeling 2 ERD cont.

SQUARES indicate TABLES LINES between them indicate the different relationships FEET at end of line, indicates a MANY relationship -1 CUSTOMER has MANY receipts -1 STAFF MEMBER sells MANY receipts -1 PRODUCT can go into many different receipts

Each product has to be INDIVIDUALLY listed 3 ERD cont. IF I wanted to know who sold me the order, I have to go to the receipt the RECEIPT table, Ill find who the STAFF MEMBER is who sold me that receipt (order ) , in

IF I wanted to know what product a CUSTOMER has ordered, I have to go to the receipt (order ), in the PRODUCT table -NO DIRECT relationship BETWEEN : CUSTOMER & STAFF, or CUSTOMER & PRODUCT 4 What are Tables or Entities Used to represent things in the real world that is of

significant interest to the user i.e. EMPLOYEES might be an Entity in a company database Employees NOUNS in a Business Narrative each table is called an entity and the columns are referred to as an attribute. within a table , each row is called a record and it represents 1 particular instance of its entity (not unlike the flat file).

5 Entities Continued Another example: A database regarding music Artist & Song are both tables Performs is the relationship (VERB) (usually is a supporting document which explains the relationship) 6

Entities and Their Attributes Entities represent something of importance Entities are made up of several Attributes (the columns), which describe the tables: ARTISTS SONGS ArtistName

SongTitle Gender Length 7 Entities Summarized Each ENTITY is made up of rows and columns Columns are called fields or attributes of a database

Rows are called records or an instance of an entity If you have repetitive VALUES to 1 attribute, create a: JOINER TABLE or LOOKUP TABLE make this attribute a Foreign key Create another table with same attribute i.e. a: Shoe Table with colours Colours will be a Foreign key, here: with ColourID primary key in a new table called: colours, with ColourID 8-10 attributes per TABLE, after that create another table

8 Entities Summarized cont. OR: When a table has the SAME VALUE REPETITIVELY for that attribute, you might need a LOOKUP TABLE: 1. People might misspell, the value, then you cant do a: SEARCH i.e. LEVEL OF EDUCATION Masters What if 1 person spells it: Masters o Another person Master

o Another person Masters of Education 9 2. You will have multiple occurences of 1 VALUE which takes up too much space in your database Steps in Database Design 1. Complete a Needs Assessment (note: this is a huge process that is not covered in this course) Basically, you speak with end users, managers, existing

documents and think outside the box If, in the design of the database, there is multiple occurrences of data (think of our gender example) this is an indicator that another table should be created. 2. Create a Business Narrative 3. Identify entities

4. Identify attributes & keys 10 5. Identify relationships 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 Identify the entities Look at NOUNS of the Business Narrative Algonquin college employs many instructors to deliver various programs comprised of many different courses. programs 12 instructors courses

When the list of Entities Seems Complete Ask about each entity: 1. Is it significant? List only entities that are important to your database users and that are worth the trouble and expense of computer tabulation. 2. Is it generic? List only types of things, not individual instances EMPLOYEES, INSTRUCTORS are generic of many different instanceswe dont say Joe 3. Is it fundamental?

List only entities that exist independently and do not need something else to explain them. Courses-explains itself, you dont need further elaboration 13 RELATIONSHIPS Three Basic Types of Relationships 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: MANYthis will be the FOREIGN KEY One to One Relationships 1:1 relationships exists when a single record in one table has 1 and only 1 corresponding record in another table, and vice versa

VERY RARE When you created a table for something, when it really should only have been a field The 1 should really have been an attribute of the other (table/entity) an attribute IMPROPERLY DEFINED as an entity i.e. 1 customer, 1 address Address should have been the attribute of the Customer table One to One Example 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:1 relationship between a Voter and a Ballot Paper. Voter Ballot One to Many Relationships MOST COMMON 1:M or M:1 Relationship exists when a record can relate to 1 OR MORE records in a 2nd table but..

A record in the 2nd table can ONLY relate to 1 record in the 1st table One to Many Example A person can own more than one car. A car can only have one owner. Owner Vehicle Many to Many Relationships

M:M Relationship exists when 1 record in either table can relate to more than 1 record in the other table 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 Many to Many Example A student can have more than one professor; the same professor can have many students

Student WATCH THIS VIDEO Professor Resolving Many to Many M:M is not allowed, as an attribute could have more than 1 value: A Multi-Valued Attribute Difficult to generate reports, or do searches Would have to repeat attributes multiple times

Instead, we create a JUNCTION or JOINER ENTITY The name of the joiner entity is often a COMBINATION of the 2 entities Student Stud_Prof Professor Many to Many Relationships

Resolving Many to Many Relationships By Creating A Junction Or Joiner Entity WATCH THIS VIDEO SHEETS OF ICE 1 arena with many sheets ARENA ID-primary key in ARENA table Add a FOREIGN KEY to SHEETS OF ICE (AREN_AID) Can a sheets of ice belong to many arenas? NOso we know

its a one-to-many TEAMS table-PRIMARY KEY: TEAM ID to PLAYERS table FOREIGN KEY: TEAM ID Relationship Review Normalization Refining the ERD

Task of analyzing entities and the relationships created have been formalized into a process called normalization. Should resolve all your relationships to One to Many (1:M) relationships Eliminate all One to Ones (1:1) and Many to Manys (M:M)

Selecting Attributes Attributes are the fields/columns that describe an entity Attributes can be: A Characteristic A Quality A Feature A Fact An attribute is a non-decomposable piece of information about an entity Cant be broken down any further, only 1 single value per column i.e. NAMElast name, 1st name Attribute Naming Conventions

Single word (or more than 1 word with an underscore (no spaces) Unique Cant be a reserved word i.e. Number, Date ( these are date types), OR name of column cant be same as name of table 28 ONLY WATCH UNTIL 5 minutes 31 seconds (until it mentions: QUERYSMITH) http://

www.youtube.com/watch?v=Y-bvjt YgRVU 29 myitlab ASSIGNMENTS-BE CAREFUL WHEN DOWNLOADING CHEATING possibilities BE CAREFUL when you are DOWNLOADING your assignment files. When you DOWNLOAD them, the files come with an embedded ENCRYPTION code

IF you COPY THE FILE from another student, your assignment will be flagged with a POTENTIAL INTEGRITY VIOLATION,(cheating). EVEN IF YOU SEND AN UNedited file, you may be legitimately doing your own work, but, the file has the ENCRYPTION code your assignment will be flagged with a POTENTIAL 37 ASSIGNMENTSREAD INSTRUCTIONS VERY, VERY

CAREFULLY!! You have to be very, very careful with ALL the components in myitlab. The software is extremely specificit IS casesensitive, and if you add a space where there shouldnt be (or vice versa), or forget to include a punctuation mark (or vice versa), myitlab will mark it wrong. When you do your assignments, you must read the instructions extremely carefully, not skimming over any of the instructions, just in case you miss a tiny requirement. 38 PRINT OUT ASSIGNMENT MARKS

Please make sure that as soon as you get your MARK for ANY assignment, that you do the following: DO NOT CLOSE the dialog box with your mark on it--before doing the following: 1. Hit your PRINT SCREEN button on your keyboard 2. Open MS WORD 3. PASTE 4. Enlarge the screen capture, so that its readable

5. PRINT out your MARK, AND SAVE the file to the DESKTOP -then UPLOAD the file to SkyDrive 6. You may now close the dialog box. There have been some students who have gotten their mark, but when they close the dialog box with the mark on it-- they receive a 0 in their GRADEBOOK. (This is the mark that I see). This print out will be your 39 backup copyput it in a safe place, until the end of the semester. SUBMITTING ASSIGNMENTS: CLICK ON: UPLOAD COMPLETED FILE

BROWSE to where your file is > UPLOAD>FINISH: SUBMIT FOR GRADING After submitting assignment, there is a time lapse. You can view results by going to VIEW SUBMISSIONS Will give 3 files: 1. SUBMITTED FILE

2. SUMMARY REPORT 3. MARKED UP REPORT 1. SUBMITTED FILE Opens a .zip file which is a COPY of the file you submitted for your assignment SUMMARY REPORT: click on the arrow to EXPAND the information, to find out exactly where you went wrong.

1. MARKED UP REPORT: Click on the check-mark or xicons to view the detailed results and score of the corresponding instruction step. If the project required you to insert images or art, those items are hyperlinked and can be selected for more details. Missing items are highlighted in tan color. Hidden elements are

highlighted in pale yellow color. Elements that were to be deleted are highlighted in orange color. REMOVE SORT HOME tab, in the SORT & FILTER group, click the REMOVE SORT button: FILTER BY SELECTION in a TABLE HOME > SORT & FILTER group > click on the FILTER

icon > To FILTER: Click on the drop-down arrow on the COLUMN HEADING (field) you want to filter Select or de-select the VALUES you want to filter NUMBER FILTERS Click on the drop-down arrow on the COLUMN HEADING (field) you want to filter NUMBER FILTERS > select one of this options:

When you select Less Than or Greater Than, by default it will Also contain Equal To, so key in The next number UP (for GREATER than), or The next number DOWN (for LESS than) I.E. If you want >200, you would have to key in >201 (as it contains equal to, and will flag 200 also) FILTER BY FORM SORT & FILTER group ADVANCED button FILTER BY FORM

FILTER BY SELECTION in a QUERY To filter all the rows in a table that contain a value that matches a selected value in a row Right-click the VALUE you want to filter. > select: EQUALS the specific value you want IF A FILTER IS ALREADY APPLIED: HOME tab, > SORT & FILTER group, > Advanced, > click: CLEAR ALL FILTERS.

ADDING A TOTAL ROW IN A QUERY QUERY TOOLS DESIGN tab, > SHOW/HIDE group, click the TOTALS button In the TOTAL row below the AMOUNT field, click the drop-down arrow and select: MIN. DESIGN tab, > RESULTS group, > RUN button. VIEW the query in DESIGN view HYBRID: MYITLAB MYITLAB HOMEWORK: 1. myitlab ASSIGNMENT: EXPLORING series

Access Chapter 1- GRADER PROJECT [HOMEWORK] 2. myitlab TRAINING: Access VOLUME 1- Skill-Based

Recently Viewed Presentations

  • DR. KRISTINE LOKKEN FUNCTIONAL NEUROPSYCHOLOGIST CO-FOUNDER/CEO BRAIN HEALTH

    DR. KRISTINE LOKKEN FUNCTIONAL NEUROPSYCHOLOGIST CO-FOUNDER/CEO BRAIN HEALTH

    T = TOXICANT REDUCTION. H = HABITS "I did then what I knew . how to do. Now that I . know better, I do better." ― Maya Angelou. Knowledge has little power . without implementation. Begin with a good...
  • Tax accounting update and tax controversy issues

    Tax accounting update and tax controversy issues

    Audit Technique Guidelines (ATGs) for Exempt Organizations. ... Form 990-EZ in the case of any IRC Section 501(c) organization or IRC Section 4947(a)(1) trust, not a private foundation and not a black lung benefit trust .
  • Vocabulary Unit 1

    Vocabulary Unit 1

    Coalition (kō ǝ lish' ǝn) N. A combination, union, or merger for some specific purpose. Synonyms: alliance, league, federation, combine. Antonym: splinter group
  • Historical Research - University of New Mexico

    Historical Research - University of New Mexico

    Historical Research ... cannot ensure representation of the sample Action Research The word "academic" is a synonym for irrelevant. ... with this statement? Why or why not? In your opinion, what is the role of academics, or outsiders, in PAR?...
  • Powertime Employee Training Manual

    Powertime Employee Training Manual

    POWERTIME TRAINING. You will learn the Employee Functions. Record and Sign Timesheets. Record and Sign Expenses. Submit Time-off Requests. View and Print Paystubs. POWERTIME TRAINING. Employees will be provided training from Payroll/HR. ... Do NOT check remember login.
  • The Sun - Weebly

    The Sun - Weebly

    This happens in the _____ Hydrogen Nuclear fusion Sun's Core Sun's Atmosphere Layers There are three layers of atmosphere around the sun: Photosphere Chromosphere Corona Temperature of these layers get hotter as they get further from the surface of the...
  • Presentation Title

    Presentation Title

    Background and Rationale. Node positive prostate cancer represents about 12% of all prostate cancers. Stage 4 disease, but potentially curable. Very few clinical trials to guide standard of care in this patient population
  • OC 2/e 13 NMR - Cengage

    OC 2/e 13 NMR - Cengage

    Calculate its IHD reference hydrocarbon C7H16 IHD = (16-14)/2 = 1 Problem: calculate the IHD for niacin, molecular formula C6H6N2O reference hydrocarbon C6H16 IHD = (16 - 6)/2 = 5 Prob 13.13 Calculate the index of hydrogen deficiency of each...