Lecture notes for 15.

566: Information Technology as an

Integrating Force in Manufacturing

What is a Database

„ An abstraction for storing and retrieving related

pieces of data
„ Many different kinds of databases have been
proposed
z hierarchical, network, etc.
z each kind supports a different abstract model for
organizing data
z in this class, we will only explain relational databases
- sets of tables of related data

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

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 1

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

The Relational Abstraction

„ Information is in tables
z Also called (base) relations

„ Columns define attributes

z Also called fields or domains

„ Rows define records

z Also called tuples

„ Cells contain values

z All cells in column have information of same type
- e.g., integer, floating point, text, date

Operating on Databases: SQL

„ Every abstraction needs an interface through

which users invoke abstract operations
z graphical interface
z language

„ Structured Query Language

„ Has all those operations
„ We'll focus only on queries
z Query = question
z Extract some data from one or more tables to answer a
particular question

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 2

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

The Select Statement

„ Every select statement yields a table of values as

output
z Sometimes there’s only one row in the table!

select columns and/or expressions

from tables

where conditions on the rows

group by group rows together

having conditions on the groups

order by order the rows

into temp save results of query in a temporary table

Display Company Data

SELECT *
FROM company;

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 3

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

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

...

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?

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 4

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

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%

Sorting

„ Can sort output by contents of a column

z sort in ascending or descending order
z 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

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 5

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

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
yr totalsales
z Aggregate those using aggregation operator
82 575837090
„ Compute total sales by all companies for
each year 83 612820552
SELECT yr,
84 721430558
Sum(sales) AS totalsales
FROM company 85 744115766
GROUP BY yr;

12

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 6

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

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 Don't combine rows unless their value in the
common column is the same
z 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 industry
codes.indname AS industry allied aerospace
FROM company, codes boeing aerospace
...
WHERE company.indcode = codes.indcode;

14

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 7

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

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 columns and/or expressions

from tables

where conditions on the rows

group by group rows together

having conditions on the groups

order by order the rows

into temp save results of query in a temporary table

16

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 8

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

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 One employee
z 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

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 9

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

Each cell holds only 1 piece of data

„ PHONE_NUM field should contain only 1 phone

number
„ If more than one phone number
z Add another column if exactly two
z 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 Last name not a key
z First name not a key
z First + middle + last may be a key
- Social security number may be a more reliable key

„ A table can have several keys

z Choose one as the primary key
„ Each table must have at least one key
z Just means no duplicate rows
z Key could be the entire set of columns
„ Key cannot be null (blank)

20

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 10

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

Tables related with foreign keys

„ Tables can be related via column(s) in common

„ Design goal
z A row in one table that refers to another table must refer to
an existing row in that table
z Example: Employee table and Department table
- Don't assign employee to department 10 if that department
doesn't exist in other table
z Foreign key design rule ensures that
„ A set of columns in table 1 is a foreign key for table 2 if:
z The foreign key takes on values from the same domain as
the primary key of table 2
z 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 Suppose employee table keeps track of department
and its address for each employee
z Address repeated for every employee in department
z What can go wrong?
- insert new employee
- modify department address
- delete last employee for department

Employee_id Dept_id Dept_address

22

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 11

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana

 Lecture notes for 15.566: Information Technology as an
Integrating Force in Manufacturing

The Design Process

„ Analyze the needs

z Queries that will be made on database
z Data entities (potential tables)
z Relationships between entities
z Constraints on data
„ Fill out the design
z What columns needed for each entity?
„ Adjust design based on checklist above
z May need to remove some columns into separate tables
z Many-to-many relationships become their own tables
- Employees table
- Projects table
- Employee assignments table

23

Lecture notes taken from 15.561 by Chrysanthos Dellarocas

Page 12

© Copyright 2003, Chrysanthos Dellarocas – Brian Subirana