AnnouncementsThe Coffee Roasting CompanyOverviewWhat is a Database?Database RulesSlide 6Slide 7Slide 8Design of the DatabaseWhat is Database Design?What is Normalization?Slide 12Overview of NormalizationNormalization RulesSlide 15First Normal Form PurposeFirst Normal Form Step 1First Normal Form Step 1 – cont’dSlide 19Slide 20First Normal Form Step 2Slide 22Slide 23Slide 24Select Primary KeySlide 26Slide 27Slide 28Slide 29Second Normal FormSlide 31Slide 32Slide 33Slide 34Slide 35Slide 36Slide 37Third Normal FormSlide 39Solution - split Supplier Information into another tableSlide 41Solution - split Supplier Shipping Information into another tableThird Normal Form TablesStill have a transitive dependencyTransitive DependencySlide 46Slide 47Receiving ReportSlide 49Slide 50Business Process AssumptionsEliminate Repeating GroupsIdentify Primary KeySlide 54Slide 55Slide 56Slide 57Slide 58Practice ExerciseSlide 60Payment VoucherPrimary Key?Slide 63Slide 64Identify Functional DependenciesSlide 66Slide 67Slide 68Common Tables?Database Schema for Purchasing CycleAssignmentSlide 72Slide 73Slide 74Slide 75Slide 76Slide 77Slide 78Slide 79Questions?AnnouncementsDifference between “excused” and “absent”Office hours next week•May not be here Monday•Tuesday: 1:00 to 2:00•Wednesday: 10:00 to 11:00•Friday: 1:30 to 2:30Questions regarding Syllabus, Class?The Coffee Roasting CompanyPhase IDesigning the DatabaseOverviewApply normalization process to purchasing cycle documents•Purchase Order•Receiving Report•Payment VoucherCombine three document sub-schemas into overall schema for purchasing cycleWhat is a Database?Collection of data organized into:•Tables (similar to a spreadsheet) – typically associated with an object/item of interest such as a customer, product, etc.–Columns represent fields•Attributes and characteristics that describe the object/item in the table•A customer has a name, address, phone, etc.–Rows represent an instance of each object/item•Plow House, Stayton OR, (503) 123-4567Database RulesOnly one type of record in each table•If put both customers and suppliers in same table, how can you tell a customer from a supplier?•If use an “external entity” table for both customers and suppliers, then a field in the table would be used to identify the type of external entity.Database RulesEach record (row) in a table must be unique•If two rows were exactly the same:•Plow House, Stayton OR, (503) 123-4567•Plow House, Stayton OR, (503) 123-4567–Is this an error – was the record entered twice by mistake–Which record should you use?Database RulesEach row within a table should have a primary key•Given that each record must be different in some way, how can you easily find a given record? That is, what field should you use to find each different record•Plow House, Stayton OR, (503) 123-4567•Plow House, Corvallis OR, (541) 789-1234•Plow House, Bend OR, (503) 987-6543•Hitching Post, Bend OR, (503) 987-1234Database RulesCharacteristics of a Primary Key•Must be unique for each row in the table•Must be stable over the life of the database–Names are not a good choice – names change–Normally assign an ID number or code•Hence extensive use of SSN–Internally generated numbers and codes will be unique and will not change•Should make sense from a business perspective–Phone number?•Easy to use–Starting to use alphanumeric codes instead of numbersDesign of the DatabasePurpose: Capture the information off of the Purchase Cycle forms and set up a database that:•Allows easy search and manipulation•Reduces amount of redundant information•Allows for future expansion and modification of dataWhat is Database Design?Process of determining the content and arrangement of data needed to support various activities.Could use a “Common-sense” approach•No way to assure that design works as intendedWe will use the “Normalization” ModelWhat is Normalization?Technique to “optimize” the design of a Relational Database•Break large complex table into smaller, simpler tables•Ensure that the smaller tables fit back together.–When break a Whole into its Parts, need to be sure you can recreate the Whole from the PartsWhat is Normalization?Must be familiar with business processes•Must understand relationships between purchase order, receiving report and payment voucher–Can multiple purchases arrive on the same receiving report?–Can a single purchase arrive on multiple receiving reports?–Can multiple purchases be paid with the same payment voucher?–If multiple shipments are received, when do we pay?Overview of Normalization1NF: Create two-dimensional tables•Identify and define relevant data elements•Remove repeating groups•Select primary key2NF: Ensure full functional dependence on Primary Key3NF: Remove transitive dependenciesNormalization RulesNo repeating groups•Typically occurs when multiple products on single form•Each product should occupy a separate row in the tableFirst Normal FormPurposeOrganize data in table •Rows and Columns•Allows flexibility for future expansionDoes not reduce redundancy•Merely an organization step to set up for normalization•Does include specification of the primary keysFirst Normal FormStep 1Identify and define relevant data elements•Usually, do not include calculated fields–Can be easily derived when needed•Exception is when need to store information for historical purposes–We will compute the sales price for a product–Once computed and conveyed to the customer, should not change the price–For example, the underlying base price may change after the order confirmation is sent to the customerFirst Normal FormStep 1 – cont’dAs identify fields, define:•Data element name•Field name within database (normally shorter without spaces or special characters)•Type of data to be stored (number, text, date, etc)•Size and format of stored data–Number can be integer, single precision, currency, …First Normal FormStep 1 – cont’dWhich items should be included from this form?First Normal FormStep 2Select relevant data elementsEliminate Repeating Groups•Repeating groups occur when multiple items allowed on form–Product name, type, pounds ordered, cost-per-pound are repeated on form•Information on each product ordered should be entered into separate row in