Downloaded 61 times
Uploaded byJoysy John
Basics of Database Design
This document discusses database design and normalization. It covers key concepts like entities, attributes, and relationships. The document demonstrates different types of relationships like one-to-one, one-to-many, and many-to-many. It provides examples of normalizing data from first normal form to third normal form to reduce redundancy and dependencies. The goal is to organize data into logical tables with primary keys and foreign keys to define relationships between the tables.
Basics of Database Design
- 1. Database Design By Joysy John CodeFirst Girls 15 Aug 2013
- 2. Agenda • Introduction • Tablesand Keys • Types of Relationships • Normalisation • Q&A
- 3. Computer Engineer, MBA Founder,Social Entrepreneur Angel Investor, Ex-Banker, Mother Education Corporate & Entrepreneurial Career Introduction
- 4. Tables and Keys •Entities: An entity is a distinct clearly identifiable object of the database e.g. Book • Attribute: Each Entity is characterised by a set of attributes e.g. Title • Entity set: Set of all entities having attributes of the same type • Relationships: A relationship is a mapping between entity sets Book Acc_No Title Author YearofPub Users Card_No Name Address Borrowed_By Card_No Acc_No DOI * 1 * 1
- 5. Sample data –library data Acc_No Name Address Title Author YearofPub CardNo DOI 4Doe Ray 4 sunshine lane Mindset Carol Dweck 2005 103 01/06/2013 8Doe Ray 4 sunshine lane Learning Mindset Carol Dweck 2006 103 01/06/2013 2Jacob John 7 Deer St Mindset Carol Dweck 2005 101 22/05/2013 1John Smith 34 London Road Happy Days A.J. Roy 2009 100 23/07/2013 5John Smith 34 London Road Learn to code Tom Close 2013 100 23/07/2013 6John Smith 34 London Road Ruby on Rails M.H. Bedi 2010 100 23/07/2013 3Mary jane 2 mill drive The art of writing M.B. Thomas 2001 102 15/08/2013 7Mary jane 2 mill drive Learn to code Tom Close 2013 102 15/08/2013 9Mary jane 2 mill drive ABC of photography K. Janice 2012 102 15/08/2013
- 6. Exercise: Managing aschool Create Students and Classes tables in a database for managing a school. • Each student can register for many classes and each class can have many students. • Students must register for a class with their firstname, lastname and age. They can select multiple classes within the same subject. • Class has a class name and belongs to a subject and is taught in venue assigned to the class.
- 7.
- 8. Basic rules: • Onetable for one entity set • One column for one attribute • One table for one relationship set • Columns are: • Key attributes of all participating entity sets • All descriptive attributes
- 9. 1:1 Relationship N:1 RelationshipN:M Relationship 1:N Relationship Types of Relationships
- 10. Example of 1:1Relationship 1 1 Person person_id first_name last_name Spouse_contact person_id Spouse_name Email Phone
- 11. Example of 1:NRelationship * 1 Person person_id name country_id Country country_id country_name capital
- 12. Example of N:MRelationship * 1 Shop shop_id shop_name location Prices product_id shop_id price Product product_id product_name category 1 * A many-to-many relationship is one where two data tables or entities have multiple rows that are connected to one or more rows in the other table.
- 13. Process of OrganisingYour Data What is Normalisation?
- 14. Why Normalise? • Reducesredundancies and dependencies • Saves space • Reduces data anomalies • Makes for easier maintenance
- 15. Normal Form • NormalForms (NF) are “standardised” rules • Each builds off the previous form • Higher the number the more normalised the data id (1NF, 2NF, 3NF, etc)
- 16. Example Data Parents studentName emergency contact student age classroom teacher school year grade level John Smith John Smith Jr John Smith 10 C110 Ms Brown 2010 6 Mary Smith John Smith Jr John Smith 10 C110 Ms Brown 2010 6 John Smith John Smith Jr Mary Smith 10 C80 Mr Green 2010 6 Mary Smith John Smith Jr Mary Smith 10 C80 Mr Green 2010 6 John Smith April Smith John Smith 9 A25 Ms Baker 2009 5 Mary Smith April Smith John Smith 9 A25 Ms Baker 2009 5 John Smith April Smith Mary Smith 9 A99 Mr Close 2009 5 Mary Smith April Smith Mary Smith 9 A99 Mr Close 2009 5 Dave Harris Julie Harris Dave Harris 6 A10 Mr Jones 2010 3
- 17. Base Table WeWill Start With
- 18. First Normal Form •Remove “repeating” data • A primary key can be defined Primary key can uniquely identify any row in a table
- 19. Parents student Nameemergency contact student age classroom teacher school year grade level John Smith John Smith Jr John Smith 10 C110 Ms Brown 2010 6 Mary Smith John Smith Jr John Smith 10 C110 Ms Brown 2010 6 John Smith John Smith Jr Mary Smith 10 C80 Mr Green 2010 6 Mary Smith John Smith Jr Mary Smith 10 C80 Mr Green 2010 6 John Smith April Smith John Smith 9 A25 Ms Baker 2009 5 Mary Smith April Smith John Smith 9 A25 Ms Baker 2009 5 John Smith April Smith Mary Smith 9 A99 Mr Close 2009 5 Mary Smith April Smith Mary Smith 9 A99 Mr Close 2009 5 Dave Harris Julie Harris Dave Harris 6 A10 Mr Jones 2010 3
- 21. Second Normal Form •Meet all requirements of 1NF • Isolate repeated subsets of data • Create the relationships
- 25.
- 26. * * * 1 Normalised tables withrelationships 1 1 * 1
- 27. Third Normal Form •Meet all requirements of 2NF • Pull out data that is not dependent on primary key
- 29.
- 30. Allow Tasks tobe assigned to a user (1:N relationship) Task task_id Name Due_date description Project_id completed User_id User user_id name * 1
- 31. Allow Tasks tobe tagged with tags (N:M relationship) Task task_id name due_date description project_id completed Tag tag_id name TagTask id tag_id task_id * 1 * 1