Readings
#1
When data creates competitive advantage and when it doesn't.
#2
Read the first chapter of the Jukic textbook.
Learn the lingo used in the chapter. It will help you to understand tech conversations in your next job. In class I will ask a few
random students to explain what the following terms mean (counts as participation). Do not memorize! It would be too much work and you need
to be able to explain it in your own words.
Metadata, database, DBMS, Front-end application, DBA, Operational vs analytical DB, software
companies that develop DBMS.
#3
Read chapter 2 of the Jukic textbook.
The chapter covers the concepts discussed in class and should be a way to make sure that you understood the contents. Stop at
"Cardinality constraints". Use the rest of the chapter to figure out what are the following (you might be asked in class to
offer an informal definition and an example):
Composite attribute, multi-valued attribute, candidate key.
#4
Read (back and forth) all the relationships in figure 2.13, describing a DB that supports the operations of the ZAGI Retail Company.
Can you do it? Write down questions to ask in class if anything is not clear.
From Chapter 3: read the requirements for a relational table (text is close to Fig. 3.1) and check your understanding with Fig. 3.1.
Chapter 2 literacy: Unary relationships, derived values
Chapter 3 literacy: Relation, Tuple, Field, Referential integrity constraint, Entity integrity constraint, Business rules, Autonumber data
type
#5
From chapter 5 of the textbook, read from the beginning to 'brief SQL syntax notes' (included), then from SELECT to nested queries
(excluded).
Note: read carefully enough to know where to go back to the book and read it more carefully when you will get stuck writing your own
queries.
Also check section 1 of this reference sheet.
Chapter 5 literacy: SELECT, DISTINCT, ORDER BY, WHERE, TOP, BETWEEN, LIKE, IN, SUM, AVG, COUNT, MAX, MIN, GROUP BY, HAVING
#6
Optional practice queries in preparation for the graded assignment
- Produce a report of our smallest ten loans
- Produce a list of the customers who live in Richmond or Roanoke (alphabetize by city first, than last name)
- How many are they?
- What is our total exposure (sum of principals) for unexpired loans? Format it nicely with a $ and two digits after the decimal point
- What is our average loan size at every level of interest rate? Order it by rate ascending
#7
From chapter 5 of the textbook, read the sections on nested queries, joins, self-join, inner and outer joins. As before, read
carefully enough to know where to go back to the book and read it more carefully when you will get stuck writing your own queries.
Also check section 2 of the reference sheet.
Chapter 5 literacy: JOIN, cartesian product, alias, UNION, INTERSECT, EXCEPT, INNER and OUTER JOIN
#8
Refresh your Chapter 5 literacy: JOIN, cartesian product, alias, UNION, INTERSECT, EXCEPT, INNER and OUTER JOIN.
Optional practice queries in preparation for the graded assignment. You will have a chance to ask questions about them in class.
- Who are our top three clients in terms of amount borrowed? Where do they live?
- What is the average rate of the loans managed by Jane Ruppell?
- Who manages ‘398’? Can you give me the name and phone number of the loan officers for that client?
- We measure loan officer performance in terms of the amount they manage. Who manages more than $500,000?
(f name, l name, lo_id and amount managed). Order by amount managed descending.
- On average, how much money does a loan officer manage?
- Prepare an alphabetized list of our insured customers. Full info.
- What is the average premium per city.
#9
I know that the class is feeling the load of a couple of midterms, so I am moving the topics that you were supposed to prepare for Wed to next Monday (i.e., reading #10). We will use Wed. to do a little more joins practice. I added a couple of questions to #8 above.
#10
From chapter 4 of the textbook, read from the beginning to the functional dependency example (included). Then from Partial functional dependency to Transitive functional dependency (included). Finally from Normalization to "a note about normalization exceptions." Overall, it is a little more pages than I would like to assign. The book is a little more detailed than I would like. Sorry! Anyhow, the goal of this chapter is to teach you how to evaluate DB designs - either your own or somebody else's for whom you might be consulting. Make sure you understand the examples about the Pressly Ad Agency more than the abstract definitions.
Chapter 4 literacy: functional dependency, non-key column, redundant data, normalization
#11
From chapter 5 of the textbook, read about DDL, DML, data types, CREATE TABLE, DROP TABLE, INSERT INTO, UPDATE, DELETE, CREATE VIEW and DROP VIEW.