Database Systems Study Guide 1Database Systems Study Guide 1Vocabulary 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 tablesExplain 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: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 spaceo 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 spaceo 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 applyo Contrast Master tables (which hold data about objects) vs. Transaction tables (which hold data about events) - give examples, for a simple process identify needed tableso 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, GPAo 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 neededExplain/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 key2) Header/detail: - 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 customer)- Usually this is supported by a foreign key e.g. the header table’s unique identifier is included in each applicable detail row- 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 transaction3) Intersection table:- If a many-to-many relationship is to be tracked in a relational database, a simple header/detail relationshipisn’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- It is not wrong to think of an Intersection table as a double header/detail relationship4) Category table:- 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 categoriesConcepts from the