Catalog Description

Prerequisite: Completion of CSCI 10 with grade of "C" or better
Hours: 72 (54 lecture, 18 laboratory)
Description: Survey of SQL (Structured Query Language). Includes database models, database design, table and view definition, transaction and data manipulation, queries and reports, data integrity, stored procedures, triggers, recovery and security. Hands-on experience using a popular SQL database. (CSU)

Course Student Learning Outcomes

  • CSLO #1: Design a database schema based on the conceptual model.
  • CSLO #2: Explain the major components of a Database Management System (DBMS).
  • CSLO #3: Use DBMS software to create a database in an Entity Relationship Diagram.
  • CSLO #4: Query the data in a relational DBMS by creating SQL commands.

Effective Term

Fall 2020

Course Type

Credit - Degree-applicable

Contact Hours

72

Outside of Class Hours

90

Total Student Learning Hours

162

Course Objectives

Lecture Objectives:
1. Design a database consisting of two or more tables from a written description of requirements. The design should include appropriately-named entities and attributes and appropriate data types.
2. Express a database design in an Entity Relationship Diagram using industry-recognized symbologies.
3. Enforce data integrity by utilizing any or all of the following constraints: primary key, foreign key, not null, unique, and check.
4. Analyze a query for potential optimizations and re-express the query in a more optimal form.
5. Analyze the schema of an un-normalized database and transform it into equivalent 1st, 2nd, and 3rd Normal Forms.
6. Identify many-to-many relationships between tables in a database design and transform them into equivalent one-to-many relationships using resolution tables.
7. Analyze tables to determine where indexes would increase the efficiency of queries. Apply indexes to the appropriate tables and fields. Test the hypothesis by measuring the increase or decrease in efficiency.
Laboratory Objectives:
1. Construct valid SQL commands to create tables corresponding to a written description or ERD.
2. Construct valid SQL commands to insert, modify, and delete data into/from the tables.
3. Construct valid SQL commands to populate tables from tab-delimited and comma-separated external data files.
4. Based on written descriptions of the data to be selected and the format and ordering of the output, construct valid SQL commands to select data from one or more tables using joins, nested subqueries, functions, and/or operators where necessary.
5. Construct valid SQL commands to create and use views of multiple tables.
6. Construct valid SQL commands to create and use stored procedures and functions.

General Education Information

  • Approved College Associate Degree GE Applicability
    • CSU GE Applicability (Recommended-requires CSU approval)
      • Cal-GETC Applicability (Recommended - Requires External Approval)
        • IGETC Applicability (Recommended-requires CSU/UC approval)

          Articulation Information

          • CSU Transferable

          Methods of Evaluation

          • Objective Examinations
            • Example: Shown is a database schema taken from an Excel spreadsheet. It is not normalized. Show the equivalent 1st, 2nd, and 3rd Normal Forms as ERDs. Explain your rationales for making the transformations you chose.
          • Problem Solving Examinations
            • Example: 1. Using the database entity relationship design shown, write the appropriate SQL commands to answer the following questions. a. How many customers are represented in the "customers" table? ANSWER: SELECT COUNT(*) FROM customers b. What is the average age of the customers in each state? Present your answer arranged from least to greatest. ANSWER: SELECT state, AVG(age) FROM customers ORDER BY AVG(age) c. Display the names of each customer and the titles of the books they have ordered this year. ANSWER: SELECT name, title FROM customer, orders WHERE customer.id = order.customer_id AND orderdate >= '2012-01-01' 2. Which data type would be most appropriate for storing a phone number? a. int b. char(20) c. blob d. date ANSWER: (b) because phone numbers can contain non-numeric characters You have begun a transaction. During the transaction you create a table and insert some data into it. Then you rollback the transaction. What happens to the table? a. The data is thrown away, but the table structure persists. b. The table structure is discarded, but the data is retained. c. Both the table and data are discarded. d. Nothing; the table and data are both retained. ANSWER: (c)
          • Projects
            • Example: Jones family opened a pizza place a couple of years ago. Their business has been successful and they are opening more stores around the town. They value customer satisfaction and they are keeping track of their customers’ complaint using a spreadsheet. Each row of the spreadsheet records information about the store, the manager, the customer and the complaint. As their business is growing, spreadsheet will not be sufficient for what they are trying to do. Design a and implement a database with the same information as they have in their spreadsheet so they can evaluate the customer satisfaction in their stores. These are the information that they keep track of in their spreadsheet: store address, phone, manager, manager id, manager phone, customer id, customer name, customer address, customer phone, complaint date, complaint time, complaint description. Rubric Grading.

          Repeatable

          No

          Methods of Instruction

          • Laboratory
          • Lecture/Discussion
          • Distance Learning

          Lab:

          1. Following an instructor discussion on SQL, the students are supplied word problems and must translate them into correct SQL commands. The instructor is on-hand to answer questions and critique answers. (Laboratory Objectives 1, 2, 3, 4, 5 & 6)

          Lecture:

          1. Instructor lecture on database designs, followed by students reading a case study and design a database from it. When done, they will compare solutions with each other. The best ones will be presented to the class for critique and analysis. (Lecture Objective 1)

          Distance Learning

          1. The instructor will present a video lecture on how to translate a written description of a database into the required tables and constraints. After the student views the lecture, a written description of a database will be given to the students to translate it to tables and constraints. (Lecture Objective 1)

          Typical Out of Class Assignments

          Reading Assignments

          1. Read the chapter about table joins. Summarize the SQL commands and options used to implement joins on a piece of paper to be used as reference during class discussion. 2. Two popular databases, PostgreSQL and MySQL, handle views differently. In particular, one of them supports updatable views and the other does not. Read the online documentation for both database systems and identify the one that does not support updatable views. Continue reading the documentation to discover how the built-in rules system can be modified to, in effect, support updatable views by overriding default behaviors. Be prepared to discuss in class.

          Writing, Problem Solving or Performance

          1. Read the case study for a video library as given in the handout. Then do the following: a. Design a database that meets the criteria expressed in the case study. Draw an entity relationship diagram corresponding to your design. b. Create the tables from your design by writing the appropriate SQL commands. c. Populate the tables from the sample data given by the instructor. d. From the data, answer the following questions: * How many videos are currently being rented? * Which customers are renting videos? * Late fees are $2 per day. How much is owed to the video rental company from videos that are late? 2. You have been given a large spreadsheet which contains data in an un-normalized form. Using the normalization techniques discussed in class and from your readings, convert the flat (one table) spreadsheet into Third Normal Form (3NF) by drawing an ERD. Construct the appropriate SQL commands to implement your design on the computer.

          Other (Term projects, research papers, portfolios, etc.)

          Required Materials

          • SQL: A Beginner's Guide
            • Author: Oppel, Andy
            • Publisher: McGraw-Hill Osborne Media
            • Publication Date: 2015
            • Text Edition: 4th
            • Classic Textbook?: No
            • OER Link:
            • OER:
          • SQL Queries for Mere Mortals
            • Author: Viescas, John L.
            • Publisher: Addison-Wesley Professional
            • Publication Date: 2018
            • Text Edition: 4th
            • Classic Textbook?: No
            • OER Link:
            • OER:
          • Database Design for Mere Mortals
            • Author: Hernandez, Michael
            • Publisher: Addison-Wesley Professional
            • Publication Date: 2013
            • Text Edition: 3rd
            • Classic Textbook?: No
            • OER Link:
            • OER:
          • The Language of SQL: How to Access Data in Relational Databases
            • Author: Larry Rockoff
            • Publisher: Course Technology PTR
            • Publication Date: 2010
            • Text Edition: 1st
            • Classic Textbook?: No
            • OER Link:
            • OER:

          Other materials and-or supplies required of students that contribute to the cost of the course.