CS 103: Lecture 3

1
CS 103 Lecture 3

• Hand-in Homework 2

2
Announcements

• Tutorials
• Tutor hours in Wing 17
• MW 6-8
• F 1-3
• Review Session Weds. 330 in 260 Main
• Office Hours
• TH 315-400 in Wing 17

3
Relational Database Terminology

• Tables (relations)
• Records (rows, tuples)
• Fields (columns, attributes)
• Data types
• Primary Key
• Foreign Key
• Query

4
Constraints

• Primary key constraint
• Referential integrity
• Entity Integrity
• Null value (an unknown value)

5
A ProblemStudent Information System

• Create a student records database. The database
  should store information about students, faculty,
  departments, courses and course sections.
  Associated with each student is a unique id, a
  name, a major and an email address. Associated
  with each faculty member is a unique id,a name
  and an email address. Departments have a unique
  name and a phone number. Course information
  includes a unique course number, a course name,
  the credit hours for the course. Faculty are
  members of one department. Each course is
  associated with one department. Each section is a
  section of one course.Section information
  includes a unique section number, a semester, a
  year, a time and a room. Each section is taught
  by one faculty member Students can enroll in many
  sections. Students are assigned a grade for each
  Section in which they are enrolled.

6
Another ProblemCAPS

• Create a database to store information about
  Customers, Products, Suppliers, and Agents.
  Associated with each customer is a unique
  customer id and a customer name. Associated with
  a product is a unique product number, a product
  name, and a list price for the product.
  Associated with a supplier is a unique supplier
  number, a supplier name and a phone number.
  Associated with each agent is a unique agent
  number and an agent name.

7
Another ProblemCAPS (continued)

• A product can be supplied by many suppliers and a
  supplier can supply many products. An agent can
  serve many customers and a customer can be served
  by many agents. Agents can have different
  commission rates for each customer they serve. An
  supplier can be used by many agents but an agent
  can only use one supplier.The database only keeps
  track of customers, products, suppliers and
  agents based on the information above. There is
  not a concept of orders in this problem.

8
Entity Relationship Modeling

• Database Design
• What data should be stored in the database?
• What tables should be created in the database?
• What are the relationships between data?
• Entities
• Attributes
• Relationships

9
Simple Problem Statement 1

• Create a database to store information about
  students. Associated with each student is a
  unique id, a name, a major and an email address.

10
ER Diagram
Students StudID SName Major Email
11
Create Tables from ER Diagrams

• Rule 1 Create a table for each entity. The key
  of the entity is used as the primary key of the
  table and all attributes become fields of the
  table

12
Create the Student Table

• Fields and data types
• StudID Text Primary Key
• Sname Text
• Major Text
• Email Text
• See Access Demonstration in Class

13
Homework 3Due 215 Thursday Sept. 13

• Create a database to store information about
  suppliers. Associated with a supplier is a unique
  supplier number, a supplier name and a phone
  number. Implement your design in Access. Use text
  for the data types for all fields.

14
Homework 3Due 215 Thursday Sept. 13

• Hand-in one page that contains the design view of
  the table.
• You can do this by using the print screen button.
  
• Press the print screen button when the table
  design view is displayed, open a Word document
  and paste into the Word document. The screen shot
  will be pasted into the document.
• Type you name and ID in the upper left had corner
  of the page.
• Print the page and bring it to class on Thursday
  September 13
• Hand-in only one page