1 / 34

CSC 110 – Fluency in Information Technology Databases

CSC 110 – Fluency in Information Technology Databases. Dr. Curry Guinn. What’s the difference between a database table and an HTML table?. CountyDat a .html. A database also defines a Structure. Metadata specifies the kind of information stored in the columns

dkeaton
Download Presentation

CSC 110 – Fluency in Information Technology Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

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

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

  9. Example Database

  10. 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

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

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

  13. 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

  14. 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

  15. 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

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

  17. 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.

  18. The Entity-Relationship diagram

  19. ER diagram between several tables

  20. Just another way of drawing the ER diagram

  21. 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.

  22. 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

  23. 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

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

  25. 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?

  26. 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?

  27. 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.

  28. 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?

  29. 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}

  30. 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?

  31. 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

  32. 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

  33. 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”?

More Related