CSC 110 – Fluency in Information Technology Databases

1. CSC 110 – Fluency in Information TechnologyDatabases Dr. Curry Guinn

2. What’s the difference between a database table and an HTML table? CountyData.html

3. A database also defines a Structure • Metadata specifies the kind of information stored in the columns • A database has a structure that is independent of the actual information • Column headings are called fields.

4. Terminology • Databases store information about entities. • An entity is represented by one row of the database table. • Entities have attributes or properties. • An attribute can have a value. • Rows and fields are, in principle, unordered.

5. So what do we need to define to create a database? • Name: Describes the kind of things that are collected in the database table. • Field Specifiers: For each column, we have to give it a • Field name: describes the attribute. • Data Format: Specifies the type of information. • Optional Comments: Useful information for the maintainer of the database.

6. The Primary Key • Usually, there is one field that can be used to uniquely identify a row in the table. • We call this field the primary key. • SSN • Telephone number. • Scientific name.

8. Specifying the Whale Table • Whales • Primary key: name

9. Database Operations • All of the database operators create new databases. • Select • Project • Union • Difference • Product

10. Example Database

11. The Select operator • The Select operator will extract rows from a database that has fields that match certain properites. • Select_fromTableOnTest • Test will be a comparison like • Dom = ‘IE’ • Lat < 50 • EW = ‘E’ AND Lat > 50

12. An example of Select • Select_from Nations On Word = ‘Beach’

13. The Project operator • Project creates a new table with only certain columns • ProjectField_ListFromTable • Project Name, Domain, One_Word From Nations

14. The Union and Difference Operators • Union combines two databases • Table1+Table2 • Difference returns the rows in the 1st database that are not in the 2nd • Table1–Table2

15. Database Design • Why is redundancy bad? • What is a relationship? • Entity Relationship Diagrams (ER diagram) • Databases and privacy • Database views • Querying: SQL • Lab: Access • Homework: Access

16. So, why is redundancy bad in a database? • What is redundancy? • Information repeated in the database • While it is wasteful from a storage (memory) standpoint, that’s not the main reason • The repeated information may be inconsistent • Changing the information in one place won’t change it in another

17. Redundancy Here? • How do we fix it? • For a movie, have a pointer to another table, a table container Directors.

18. Database Relationships • How are tables related to one another? • Entities between tables may share a relationship. • For instance, one table contains a Student’s ID, name, and birthdate. • Another contains a Student’s ID and address. • The relationship between the two tables is Lives_At.

19. The Entity-Relationship diagram

20. ER diagram between several tables

21. Just another way of drawing the ER diagram

22. Separating Information for Privacy • In a master database of student information, some data may be sensitive. • Academic probation status, for instance. • Don’t put this data in a table that might be accessed by many sources. • For instance, if you put that field in the table that contains the student’s name, that data would be widely accessible. • Separate tables can be given different levels of accessibility.

23. Database Views • Different users of a database are interested in different aspects of the information. • Your instructor might want to see your current course schedule but your birth date is probably not relevant. • Creating the instructor’s view would combine only certain of the tables, say Student ⋈ Classes ⋈ Transcripts

24. SQL (Structured Query Language) • SQL is a international standard database query language • Simple queries look like: SELECT List of fields FROM Tables WHERE Constraints on the rows SELECT LastName, FirstName FROM employees WHERE DeptID=3

25. Lab: Database Example using Microsoft Access • Download the following file to your machine: movies.mdb. • Open the file by double-clicking.

26. What’s in the database? • Tables: Movie and Director • Queries: Ways of building new tables • Open the Movie table. • By default you get the “datasheet” view. • How does it resemble a spreadsheet? • Grids of cells in rows and columns • Can select rows, columns, rectangular areas • What are the field names of this table? • MovieID, DirectorID, Title, ReleaseYear • What is the primary key?

27. Different ways to view the table • Take a look at the “Design” view • Menu… View – Design View or • Do you see the symbol to indicate the primary key?

28. Relationships • Close the Movie table and open up the Relationships tool • Tools – Relationships or • Each window represents a table • The black line connects and you can see there is a 1 to many (∞) relationship. • The primary keys are in bold.

29. Queries • A query gives instructions for building a new table. • Open the Queries window. • Double-click on MoviesBefore1980. • What do you see? • Are they listed in any particular order? • How did they do that?

30. Examining queries • Single-click on MoviesBefore1980 • Hit the Design button • Now you see the SQL-like query structure: • Field: ReleaseYear and Title • Database: Movie and Movie • Sort: Ascending and Ascending • Criteria: <1980 and {none}

31. Modifying the Query • Changing the sorting order • Try it • Single-click on the Release Years: Sort • Change it to Descending • Now “run” the query by double-clicking on the query • Change the selection criteria • How about movies after 1980?

32. Creating a new query • Let’s make a query to find all the directors whose names start with “s”. • Click on “Create Query in Design View” • In the Show Table dialog box, select “Director” then “Close” • The fields we are interested in are LastName and FirstName • Sort however you want • Type “s*” in the Criteria field • Try it out

33. Queries with Joins • Suppose we want a query that combines information from more than 1 table • Build a query that will show the director’s last and first name, birth year, and movie titles where the director was born after 1955. • “Create Query in Design View” • Choose both tables and then close. • Choose the appropriate fields. • Choose the appropriate criteria • Sort however you want • Try it

34. Homework: Database QueriesDue: 11:59pm 11/13 • Using the movies.mdb database, design the following queries: • All the movies made in 1980. • All the movies made in the 1980s (1980-1989). List the director’s last and first name. Also the movies should be listed chronologically, from oldest to newest. • A query that returns each Psycho film, the year made, and who the director was. List the oldest version first. • Which movies have titles that start with “R”?