Database Systems Study Guide 1 Vocabulary you should be able recognize explain and apply these terms Database a collection of related files containing records on people places or things Relational databases organize data into 2 D tables with rows and columns Objects person place or thing Attributes specific characteristics of entities or events The smallest unit of data with meaning to a user columns fields Database management system DBMS software for creating storing organizing and accessing data from a database RDBMS Relational DBMS a Schema describes the logical structure of a database a Logical View See page 173 Table Each type of entity or event gets its own table aka a relation Record a row in a table corresponds to one item e g each customer has its own record aka tuple Attributes The smallest unit of data with meaning to a user columns fields Primary Key An attribute or combination of attributes that uniquely identifies a row Foreign Key A field in one table that refers to the primary key of another a lookup field Referential Integrity Rules that enforce defined relationships between tables Explain the modeling approach to data design o Semantic modeling identifies entities and transactions and then builds from there Be sure you understand the steps we used to generate a data design using semantic modeling You don t have to list these but you should understand how to proceed and understand what each step involves Identify transactions and related objects Analyze cardinality many to many Categorize and establish foreign keys Explain some problems with redundancy It makes it hard to change It makes it hard to find It wastes space o What happens a bit of reference data is duplicated in several rows in a table o Or you can say it causes Anomalies Be prepared to give an example of each of these in the context of your group project e g If we didn t keep a separate customer table But because we do Explain some problems with redundancy o What happens a bit of reference data is duplicated in several rows in a table It makes it hard to change It makes it hard to find It wastes space o Or you can say it causes Anomalies Be prepared to give an example of each of these in the context of your group project e g If we didn t keep a separate customer table But because we do Update An update anomaly occurs when because the same bit of data is stored in multiple places a system must be updated in many places to make a single logical change Find it everywhere to change it Insert An insert anomaly occurs when because data two or more different types of objects or events are stored in the same table there is no way to record something at the right time For example if the only place you list sales rep data is in the invoice table how can you add a sales rep who hasn t sold anything yet Delete A delete anomaly occurs when because data two or more different types of objects or events are stored in the same table deleting information about one thing also removes all record of another object or event of interest For example if data about a customer is only stored in the invoice record removing an invoice from the table might eliminate all record of a particular customer A Semantic Modeling Approach Note special definitions of the terms as I will use them in class in the real world the terms may be applied differently but the understanding will still apply o Contrast Master tables which hold data about objects vs Transaction tables which hold data about events give examples for a simple process identify needed tables Compare and contrast Reference vs Calculated data Reference data is about the object they do not change much over time changes are file maintenance examples customer address or name part size or price Summary data is affected by the transactions related to an object they are updated either when transactions occur or at other times as appropriate examples beginning balance YTD sales Qty on hand GPA Cardinality Explain 1 1 1 m and m m cardinality with examples How many of entity A over time for each instance of B Be prepared to determine the cardinalities for a simple example and identify the attributes which should appear in an intersection table when it is needed o o Explain apply these 4 design patterns 1 Transaction Objects Transactions are recorded events e g sell invoices enroll in a class or deliver goods Objects usually exist over time e g customers courses or products Remember summary and reference data By assigning each object a unique identifying value id number and including that value in related transactions we avoid many processing anomalies Use a foreign key 2 Header detail customer applicable detail row 3 Intersection table One row in a header file applies to several rows in the detail table Example Customer at a dealership header with multiple contacts times a sales person speaks to the Usually this is supported by a foreign key e g the header table s unique identifier is included in each Often but not always headers are master tables and details are transactions Sometimes the header is a transaction e g an invoice with multiple detail records further describing the transaction If a many to many relationship is to be tracked in a relational database a simple header detail relationship isn t sufficient an intersection table is needed Intersection tables link other objects transactions Intersection tables include a primary key for the other tables and some additional information to characterize the relationship 4 Category table It is not wrong to think of an Intersection table as a double header detail relationship For applying one of several mutually exclusive categories to each row in a table Create a second table a category table with an identifier and a description include a column in the original table to hold a value from the category table support with a foreign key Example a student can optionally selects one of 5 meal plans a lookup table lists plans 1 5 the student record has a Meal Plan column with a value of 1 5 Category tables avoid ambiguous spelling problems enforce categories Concepts from the database in class assignment are also considered fair game for quizzes and exams Be prepared to explain the function of each of the SQL commands we discussed including Select From Where Join Other commands not used in our exercises are often used by programs which interact with SQL Insert which can be used to add data to a table without using a visual interface