CSCI 0052. Introduction to SQL
Units: 3
Prerequisite: Completion of CSCI 10 with grade of "C" or better
Hours: 72 (54 lecture, 18 laboratory)
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)
CSCI 0052 - Introduction to SQL
http://catalog.sierracollege.edu/course-outlines/csci-0052/
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: 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: 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 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.
Course Identification Numbering System (C-ID)
...COMP 122 CSCI 0012 COMP 132 CSCI 0013...0055 AJ 122 ADMJ 0052 AJ 124 ADMJ...
