Introduction to Databases, Data Modeling and XML

What is a Database „ An abstraction for storing and retrieving related pieces of data „ Many different kinds of databases have been proposed z z z hierarchical, network, etc. each kind supports a different abstract model for organizing data in this class, we will only explain relational databases - sets of tables of related data 1 Example DB: Fortune 500 Companies „ company compname sales assets netincome empls indcode yr allied 9115000 13271000 -279000 143800 37 85 boeing 9035000 7593000 292000 95700 37 82 ... „ industry codes indcode indname 42 pharmaceuticals 44 computers ... 2 Page 1 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana The Relational Abstraction „ Information is in tables z „ Columns define attributes z „ Also called fields or domains Rows define records z „ Also called (base) relations Also called tuples Cells contain values z All cells in column have information of same type - e.g., integer, floating point, text, date 3 Operating on Databases: SQL „ Every abstraction needs an interface through which users invoke abstract operations z z graphical interface language „ Structured Query Language „ Has all those operations „ We'll focus only on queries z z Query = question Extract some data from one or more tables to answer a particular question 4 Page 2 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana The Select Statement „ Every select statement yields a table of values as output z Sometimes there’s only one row in the table! select from where group by having order by into temp columns and/or expressions tables conditions on the rows group rows together conditions on the groups order the rows save results of query in a temporary table 5 Display Company Data SELECT * FROM company; 6 Page 3 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana Choose Columns „ Choosing a subset of columns is sometimes called "project" operation „ Display company name and income for each year „ SELECT compname, netincome, yr FROM company; compname netincome yr allied -279000 85 boeing 292000 82 ... 7 Choose Rows „ Find performance data for 1984 for boeing SELECT compname, netincome, yr FROM company WHERE yr = 84 AND compname = “boeing”; „ Which companies lost money in 1984? 8 Page 4 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana Compute Columns „ Find return on assets for each year SELECT compname, yr, (netincome/assets) AS roa FROM company; „ Nice names for output columns z „ Name following computed column (e.g., roa) will be used to name output column Find company-years with roa of more than 15% 9 Sorting „ Can sort output by contents of a column z z „ sort in ascending or descending order sort by more than one column (second one breaks ties) Sort companies by 1984 profits SELECT compname, netincome FROM company WHERE yr = 84 ORDER BY netincome DESC; „ Sort companies by 1984 return on assets 10 Page 5 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana Aggregates Can make calculations on entire columns „ z sum, avg, max, min, count How many apparel companies are in database and what are their total sales for 1984? SELECT Count(*) AS number, Sum(sales) AS totalsales „ FROM company WHERE indcode = 40 and yr = 84; z returns a table with just one row! What is average percent roa for apparel companies in 1984? „ 11 Grouping and Aggregates „ Each different value for the group by fields defines a new group „ One row of output is produced for each group „ Several rows may belong to same group z „ Aggregate those using aggregation operator Compute total sales by all companies for each year SELECT yr, Sum(sales) AS totalsales FROM company GROUP BY yr; yr totalsales 82 575837090 83 612820552 84 721430558 85 744115766 12 Page 6 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana More examples „ Compute total sales by all companies for each year SELECT yr, Sum(sales) AS totalsales FROM company GROUP BY yr; „ Compute total sales for each company „ What are the leading industries in total sales for 1984? 13 Joins „ Combine rows from one table with rows from another „ Usually join on some common column z z „ Don't combine rows unless their value in the common column is the same Where clause says the common column must be same in each table Find the industry name for each company SELECT company.compname AS compname, compname codes.indname AS industry allied FROM company, codes boeing ... WHERE company.indcode = codes.indcode; industry aerospace aerospace 14 Page 7 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana Example DB: Fortune 500 Companies „ company compname sales assets netincome empls indcode yr allied 9115000 13271000 -279000 143800 37 85 boeing 9035000 7593000 292000 95700 37 82 ... „ industry codes indcode indname 42 pharmaceuticals 44 computers ... 15 SQL Summary select from where group by having order by into temp columns and/or expressions tables conditions on the rows group rows together conditions on the groups order the rows save results of query in a temporary table 16 Page 8 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana Database Design Checklist „ Meaningful tables „ Each cell holds only 1 piece of data „ Each table has a key „ Tables related with foreign keys „ Avoid redundant storage of data „ Minimize empty cells 17 Meaningful Tables „ Each row should represent one instance of an entity or relationship z z „ One employee One project-employee relationship One table should not contain data about several entities z E.g., employee id and department location in separate tables „ Even though employee is currently assigned to a department, which has a location Easier to update if employee switches departments Litmus test: succinct answer to: z “What’s in this table?” 18 Page 9 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana Each cell holds only 1 piece of data „ PHONE_NUM field should contain only 1 phone number „ If more than one phone number z z Add another column if exactly two Separate phone numbers table if number of phones not predetermined Employee_id Phone1 Phone2 19 Each table has a key „ Key: a set of columns that picks out a unique row from the table z z z Last name not a key First name not a key First + middle + last may be a key - „ A table can have several keys z „ Choose one as the primary key Each table must have at least one key z z „ Social security number may be a more reliable key Just means no duplicate rows Key could be the entire set of columns Key cannot be null (blank) 20 Page 10 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana Tables related with foreign keys „ Tables can be related via column(s) in common „ Design goal z z A row in one table that refers to another table must refer to an existing row in that table Example: Employee table and Department table - z „ Don't assign employee to department 10 if that department doesn't exist in other table Foreign key design rule ensures that A set of columns in table 1 is a foreign key for table 2 if: z z The foreign key takes on values from the same domain as the primary key of table 2 When the value of the foreign key in table 1 is not null, there is a row in table 2 that has that value 21 Avoid redundant storage of data „ Redundant storage is wasteful „ Example z z z Suppose employee table keeps track of department and its address for each employee Address repeated for every employee in department What can go wrong? - insert new employee modify department address delete last employee for department Employee_id Dept_id Dept_address 22 Page 11 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana The Design Process „ Analyze the needs z z z z „ Fill out the design z „ Queries that will be made on database Data entities (potential tables) Relationships between entities Constraints on data What columns needed for each entity? Adjust design based on checklist above z z May need to remove some columns into separate tables Many-to-many relationships become their own tables - Employees table Projects table Employee assignments table 23 Page 12 © Copyright 2003, Chrysanthos Dellarocas – Brian Subirana