An Introduction to Databases

1. An Introduction to Databases January 14, 2010

2. Groups • 21 people = 3 4 person groups and 3 3 person groups • Group roles – These may rotate during the semester • “Secretary/Recorder” – person to document decisions made by the group. This person should carefully document team member responsibilities as defined in the meeting. • “Chair/Coordinator” – person responsible for keeping the meetings going. Keeps the group from getting too far off track. (Some socialization is good and important, but too much can keep you from meeting your goals.) • Contact person – the person responsible for contacting customers or “surrogates”. (I don’t want questions coming from many different people.)

3. And the groups are: • Friday – early afternoon – Cordazzi, Lloyd, Coles, Tucker • Sunday – 3 – 6 – Brubaker, Tian, Dunaway • Wednesday – 3 – 6 – Stacey, Kim, Ringers, Hubbard • Wednesday – 3 – 6 – Vandenberger, Miles, Danielson • Sunday – 6 – 10 – Stanley, Foster, Luong, Smith • Thursday – 6 – 10 – Ford, Holt, Onat

4. In your groups • Find out a little bit about each other. • Trade contact information. • Confirm the official time for team meetings. You may want to use those meetings to prepare homework as well as to work on the project. You will turn in individual homework, but may work on it together. • Choose a team / company name. This will be your software company name and will be used through the semester.

5. In your groups • Create a Team Expectation Agreement • This document will list the expected behavior of team members both inside of class when doing group work and outside of class when working on homework and project work. • Examples: • Team members will make sure that we all attend class each day. • We will make sure that each team member contributes in some way to each group meeting. • We agree to let each other know if one person is monopolizing the conversations in class. • Assign to the recorder the task of recording the group agreement. The recorder should then send an electronic copy to each of the group members for approval. When done, send one copy to me. (Due Tuesday)

6. Which project? • In your groups, discuss which project you would find most interesting. • Let’s discuss as a class

7. Questions about contract grades? • Fill out a contract (found under assignments) and return no later than Tuesday. • You will be able to change your contract once during the first half of the semester (before spring break).

8. Evaluation • Team members will evaluate the effectiveness of each member of the team. If an individual is not contributing to the project, they should not receive the same grade as those that do. • We will do an early evaluation after the project begins and then do a final one as the project is turned in. The project grade will be based on the quality of the project (group grade) and the value of the contribution of the individual.

9. An introduction to database systems • Here are some examples of computer database applications • E-campus (PeopleSoft) • Blackboard • E-commerce (Amazon.com) • Library • What attributes do these systems all share?

10. More formal definitions • A collection of related data. • Card catalog • Recipe file • Phone directory • A collection of related data stored in a computer system. • Definition from my old book. • This data simulates some real world entities. It usually is used to focus on how those entities are used within a particular process. • The entities are related to one another. • Teacher – Class Student – Class • The relationships may be one to one (One person has at most one spouse), one to many (one teacher may have many classes) or many to many (one customer may buy many items and each item may be bought by many customers)

11. More about database properties • The data in a database should be logically related (we talk about integrated databases). • The data should have meaning to its user community. • The data should be useful to someone within the user community. • Databases are not static collections of data. The data changes over time. The database design should be based on how the data will be used.

12. Information vs data • Data is the raw values. For example 3 is data. • But what does it mean? • Your GPA? • Interest rate percentage? • Average life expectancy of an American Newt (http://www.myuniversalfacts.com/2007/01/life-span-of-animals-how-long-do.html) • Databases provide structure to data which helps us to understand the data in its context. • The data becomes information when its meaning is understood.

13. Properties of databases • Data is integrated • Data is shared among many users • Data has persistence – it lives over a period of time • Data is not duplicated to the extent possible • Data is stored in raw form and then is processed

14. DBMS • Database Management Systems • Manage the database • Provide an application that lets DBAs manipulate the database • DBA is Database Administrator • Database Management System features • Provide a mechanism for defining the data (meta-data) • Provide a mechanism for protecting the data • Provide a query mechanism for accessing the data • Applications can interact with the DBMS to perform database actions.

15. Why DBMS • Efficient way to manage data • Prevent data redundancy (data found in several places – sometimes inconsistently) • Enforce security constraints – provide different views • Student user has one level of access • Professors have another • Database administrator has another • Enforce “business rules” of the organization • Dates should be dates • Grades must be particular values only

16. People • End users • Casual users (use queries) • Naïve users (use applications) • Secondary users (use output from db) • Applications programmers • Write the applications that interact with the database • Data administrator • Designs, creates and maintains the database • Implements the technical controls to enforce policies • Database Administrator • Strategy decisions, policy decisions

17. A database system • Diagram • Components – users • Data layer – resides on the physical hardware • DBMS – provides the interface to the physical data

18. DBMS • Categories • Model • Hierarchical – Tree like structure – parent to child segments – one to many. • Network – Like hierarchical but with the possibility of many to many relationships. • Relational – 1970 – Codd – The data is represented as a series of related tables. • Object Oriented – Relational but with the inclusion of methods to form full objects. • Users • Single user at a time • Multiple users • Sites • Centralized • Distributed – database or parts are divided up into multiple locales • Client/server – central data but applications run on local machines

19. Example – Marina Service Business What’s wrong with this picture?

20. Two tables, Marina…

21. And the Technician table

22. Connection

23. Tech number The Tech Number in the Marina table is a Foreign Key to the unique identifier in the Technician table.

24. What do we want to see? • A list of the names and warrantee amounts from the table • A list of all of the zip codes from the marinas • A list of all of the marinas with warrantees • A list of all of the cities where marinas from Michigan are located sorted by city name

25. Basic queriesGiven the Marina Table – what do we get? • SELECT name, warranty FROM Marina • SELECT zip code FROM Marina • SELECT name FROM Marina WHERE warranty > 0 • SELECT city FROM Marina WHERE state = “MI” ORDER BY city