1
|
- Week 8
- LBSC 690
- Information Technology
|
2
|
- Cardinality
- Compound primary keys
- Derivable combinations (5NF)
|
3
|
|
4
|
- If you sell product type P
- And you sell brand B
- And B makes P
- Then you sell B’s P
|
5
|
- Questions
- E-R modeling exercise
- Microsoft Access
- Scalability issues
- MySQL
- Term project
|
6
|
- Databases are a good choice when you have
- Lots of data
- A problem that contains inherent relationships
- Join is the most important concept
- Project and restrict just remove undesired stuff
- Design before you implement
- Managing complexity is important
|
7
|
- Design a database to match passengers with available rides for Spring =
Break
- Drivers phone in available seats
- They want to know about interested passengers
- Passengers call up looking for rides
- They want to know about available rides
- No “ride wanted” ads
- These things happen in no particular order
|
8
|
- Identify the tables you will need
- First decide what data you will need
- What questions will be asked?
- Then design normalized tables
- Start with binary relations if that helps
- Design the queries
- Using join, project and restrict
- What happens when a passenger calls?
- What happens when a driver calls?
|
9
|
- What questions must you answer?
- What data is needed to generate the answers?
- Entities
- Attributes of those entities
- Relationships
- Nature of those relationships
- How will the user interact with the system?
- Relating the question to the available data
- Expressing the answer in a useful form
|
10
|
- Work in groups of 3 or 4
- Brainstorm data requirements for 5 minutes
- Do passengers care about the price?
- Do drivers care how much luggage there is?
- Develop tables and queries for 15 minutes
- Don’t get hung up on one thing too long
- Compare you answers with another group
- Should take about 5 minutes each
|
11
|
- Pick a primary key for each entity
- Build the tables
- One per entity
- Plus one per M:M relationship
- Choose terse but memorable table and field names
- Check for parsimonious representation
- Relational “normalization”
- Redundant storage of computable values
- Implement using a DBMS
|
12
|
- 1NF: Single-valued indivisible (atomic) attributes
- Split “Doug Oard” to two attributes as (“DougR=
21;,
“Oard”)
- Model M:M implement-role relationship with a table
- 2NF: Attributes depend on complete primary key
- (id, impl-role, name)->(id, name)+(id, impl-role)
- 3NF: Attributes depend directly on primary key
- (id, addr, city, state, zip)->(id, addr, zip)+(zip, city, state)=
- 4NF: Divide independent M:M tables
- (id, role, courses) -> (id, role) + (id, courses)
- 5NF: Don’t enumerate derivable combinations
|
13
|
- Create a database called M:\rides.mdb
- File->New->Blank Database
- Specify the fields (columns)
- “Create a Table in Design View”
- Fill in the records (rows)
- Double-click on the i=
con
for the table
|
14
|
- Enter field name
- Must be unique, but only within the same table
- Select field type from a menu
- Use date/time for times
- Use text for phone numbers
- Designate primary key (right mouse button)
- Save the table
- That’s when you get to assign a table name
|
15
|
- Open the table
- Enter new data in the bottom row
- A new (blank) bottom row will appear
- Close the table
- No need to “save” – data is stored automatically<=
/li>
|
16
|
- Copy ride.mdb to your M:\ drive
- “Create Query in Design View”
- Choose two tables, Flight and Company
- Pick each field you need using the menus
- Unclick “show” to not project
- Enter a criterion to “restrict”
- Save, exit, and reselect to run the query
|
17
|
- Joins are automatic if field names are same
- Otherwise, drag a line between the fields
- Sort order is easy to specify
- Queries form the basis for reports
- Reports give good control over layout
- Use the report wizard - the formats are complex
- Forms manage input better than raw tables
- Invalid data can be identified when input
- Graphics can be incorporated
|
18
|
- Some typical database applications:
- Banking (e.g., saving/checking accounts)
- Trading (e.g., stocks)
- Airline reservations
- Characteristics:
- Lots of data
- Lots of concurrent access
- Must have fast access
- “Mission critical”
|
19
|
|
20
|
- Registrar database must be internally consistent
- Enrolled students must have an entry in student table
- Courses must have a name
- What happens:
- When a student withdraws from the university?
- When a course is taken off the books?
|
21
|
- Conditions that must always be true
- Specified when the database is designed
- Checked when the database is modified
- RDBMS ensures integrity constraints are respected
- So database contents remain faithful to real world
- Helps avoid data entry errors
|
22
|
- Foreign key values must exist in other table
- If not, those records cannot be joined
- Can be enforced when data is added
- Associate a primary key with each foreign key
- Helps avoid erroneous data
- Only need to ensure data quality for primary keys
|
23
|
- Thought experiment: You and your project partner are editing the same
file…
- Scenario 1: you both save it at the same time
- Scenario 2: you save first, but before it’s done saving, your
partner saves
|
24
|
- Possible actions on a checking account
- Deposit check (read balance, write new balance)
- Cash check (read balance, write new balance)
- Scenario:
- Current balance: $500
- You try to deposit a $50 check and someone tries to cash a $100 che=
ck
at the same time
- Possible sequences: (what happens in each case?)
|
25
|
- Transaction: sequence of grouped database actions
- e.g., transfer $500 from checking to savings
- “ACID” properties
- Atomicity
- Consistency
- Each transaction must take the DB between consistent states.
- Isolation:
- Concurrent transactions must appear to run in isolation
- Durability
- Results of transactions must survive even if systems crash
|
26
|
- Idea: keep a log (history) of all actions carried out while executing
transactions
- Before a change is made to the database, the corresponding log entr=
y is
forced to a safe location
- Recovering from a crash:
- Effects of partially executed transactions are undone
- Effects of committed transactions are redone
|
27
|
|
28
|
- Remote access to a database
- Client does not need the database software
- Serve rapidly changing information
- e.g., Airline reservation systems
- Provide multiple “access points”
- By subject, by date, by author, …
- Record user responses in the database
|
29
|
- Benefits
- Multiple views
- Data reuse
- Scalable
- Access control
- Costs
- Formal modeling
- Complex (learn, design, implement, debug)
- Brittle (relies on multiple communicating servers)
- Not crawlable
|
30
|
- Displays database content on Web pages
- Not very useful for changing database content
- Drag-and-drop design in Microsoft Access
- “Reports” are designed for printing
- “Pages” are designed for the Web
- Requirements:
- Microsoft Web Server (not Apache)
- IE 5 or higher Web browser (not Firefox)
- “Office Web Components” on client machine
- IE 7 fails gracelessly without them!
|
31
|
|
32
|
|
33
|
|
34
|
|
35
|
|
36
|
- SELECT Company.CompanyName, Company.CompanyPhone, Flight.Origin,
Flight.DepartureTime
- FROM Flight,Company
- WHERE Flight.CompanyName=3DCompany.CompanyName
- AND
Flight.AvailableSeats>3;
|
37
|
|
38
|
|
39
|
- “root” user creates database + grants permissions
- Using the WAMP console (or mysql –u root –p)
- root has no initial password; just hit <enter> when asked
- By the system administrator on OTAL (otal.umd.edu)
-
CREATE DATABASE project;
-
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, =
DROP
ON project.* TO ‘foo’@’localhost’ IDENTIFIE=
D BY
‘bar’;
-
FLUSH PRIVILEGES;
- Start mysql
- MySQL console for WAMP, ssh for OTAL
- &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p; &=
nbsp; &nbs=
p;
mysql –u foo –p bar
- Connect to your database
|
40
|
- CREATE TABLE contacts (
- ckey MEDIUMINT UNSIGN=
ED NOT
NULL AUTO_INCREMENT,
- id MEDIUMINT UNSIGNE=
D NOT
NULL,
- ctype SMALLINT UNSIGN=
ED NOT
NULL,
- cstring VARCHAR(40) N=
OT
NULL,
- FOREIGN KEY (id) REFE=
RENCES
persons(id) ON DELETE CASCADE,
- FOREIGN KEY (ctype)
REFERENCES ctlabels(ctype) ON DELETE RESTRICT,
- PRIMARY KEY (ckey)
- ) ENGINE=3DINNODB;
- To delete: DROP TABLE contacts;
|
41
|
- INSERT INTO ctlabels
- (string) VALUES
- ('primary email'),
- ('alternate email'),<=
/li>
- ('home phone'),
- ('cell phone'),
- ('work phone'),
- ('AOL IM'),
- ('Yahoo Chat'),
- ('MSN Messenger'),
- (‘other’)=
;
- To empty a table: DELETE FROM ctlabels;
|
42
|
- SHOW DATABASES;
- SHOW TABLES;
- DESCRIBE tablename;
- SELECT * FROM tablename;
|
43
|
- Design a database to keep track of service calls for a utility compa=
ny:
- Customers call to report problems
- Call center manages “tickets” to assign workers to jobs=
- Must match skills and service location
- Must balance number of assignments
- Workers call in to ask where their next jobs are
- In SQL, you can do the following operations:
- Count the number of rows in a result set
- Sort the result set according to a field
- Find the maximum and minimum value of a field
|
44
|
- Goal is to learn some things deeply
- At least two key technologies
- Web, database, programming, synchronized media
- 4-minute overview talk next week
- 3 slides (goals, mockup, scope)
- 12-minute presentation in final class session
- Written report due by email 2 days later
|
45
|
- On a sheet of paper, answer the following (ungraded) question (no
names, please):
- What was the muddiest point in today’s class?
|