|Student Learning Outcomes -|
- Create a conceptual database design
- Use Structured Query Language to perform queries on a database
|Description - |
|Introduction to database design and use of database management systems for applications. Topics include database architecture, comparison to file-based systems, historical data models, conceptual model; integrity constraints and triggers; functional dependencies and normal forms; relational model, algebra, database processing and Structured Query Language (SQL), database access from Applications-Embedded SQL, JDBC,Cursors, Dynamic SQL, Stored Procedures. Emerging trends will be studied, such as NoSQL databases, Internet & Databases and On-Line Analytical Processing (OLAP). A team project that builds a database application for a real-world scenario is an important element of the course.|
|Course Objectives - |
|The student will be able to: |
- Examine the problems with file-based systems and the advantages of the database approach.
- Distinguish between the three levels in the architecture of a typical database management system.
- Practice conceptual database design through entity-relationship(ER), enhanced ER models. Describe models of historical interest such as Network and Hierarchical model.
- Design and model a database application using the relational model. Design by ER and EER to relational mapping.
- Define and apply integrity constraints and triggers; Tune design using functional dependencies and normal forms.
- Use Structured Query Language to perform queries and to perform relational operations.
- Understand emerging database technologies and applications.
|Special Facilities and/or Equipment - |
- Access to a commercial database management system such as Oracle or MySQL.
- A website or course management system with an assignment posting component (through which all lab assignments are to be submitted) and a forum component (where students can discuss course material and receive help from the instructor). This applies to all sections, including on-campus (i.e., face-to-face) offerings.
- When taught via Foothill Global Access on the Internet, the college will provide a fully functional and maintained course management system through which the instructor and students can interact.
- When taught via Foothill Global Access on the Internet, students must have currently existing e-mail accounts and ongoing access to computers with internet capabilities.
|Course Content (Body of knowledge) - |
- Introduction to Database Systems
- Why Databases?
- File Systems versus DBMS
- Roles of the database management system (DBMS)
- Types of database management systems
- Description, data requirements and transaction requirements
- Introduction to Database Design
- DBMS architecture and data independence
- The process for designing a database in industry
- Database Languages
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Relational Model
- Historical perspective: hierarchical and network models
- Relational data structure
- Relational algebra
- Logical Design of the Application
- Conceptual Design of the Application
- Modelling relationships in the data
- Entity Relationship (ER) and Extended Entity Relationship (EER) Modeling Concepts
- Conceptual Design with ER and EER Modeling
- Logical Database Design: ER to Relational Mapping
- Database Application Development
- Normalization of databases
- Integrity constraints
- Tuning the database design
- Structured Query Language (SQL)
- Data Definition Commands
- Data Manipulation Commands
- SELECT Queries
- Relational algebra operations
- Cursors, Dynamic SQL, Stored Procedures
- Embedded SQL (SQLJ), JDBC
- Virtual Tables-Views
- Advanced SQL: Set Operators, Join Operators, Subqueries and Correlated Queries, SQL Functions, Procedural SQL
- Emerging Trends in Database
- Introduction to NoSQL Databases
- Designing NoSQL databases
- Comparison of NoSQL Databases
- Internet & Databases
- Introduction to On-Line Analytical Processing (OLAP)
|Methods of Evaluation - |
- Exams and quizzes
- SQL programming assignments
- Team project that designs and implements a database application for a real-world scenario
|Representative Text(s) - |
|Coronel,Carlos, Morris, Steven and Rob, Peter. Database Systems: Design, Implementation and Management, 10th edition, 2013. |
Hoffer,Jeffrey A., Ramesh, V., Topi, Heikki. Modern Database Management,11 th edition, 2012.
|Disciplines - |
|Computer Science |
|Method of Instruction - |
- On-line labs (including sections meeting face-to-face/on campus) consisting of
- An assignment web-page located on a college-hosted course management system or other department-approved Internet environment. Here, the students will review the specification of each assignment and submit their completed lab work.
- A discussion webpage located on a college hosted course management system or other department-approved Internet environment. Here, students can request assistance from the instructor and interact publically with other class members.
- Detailed review of assignment that includes model solutions and specific comments on the student submissions.
- In person or on-line discussion, which engages students and instructor in an ongoing dialog, pertaining to all aspects of database management systems.
- When course is taught fully on-line:
- Instructor-authored lecture materials, handouts, syllabus, assignments, tests, and other relevant course material will be delivered through a college hosted course management system or other department-approved Internet environment
|Lab Content - |
- Generate a database application (DA) from an existing database, based on a real-world scenario:
- Write detailed specifications for the DA
- Write data requirements for the DA
- Write transaction requirements for the DA
- Design a conceptual model of the database application using ER and EER models.
- Complete logical design of the database application---ER to relational schema mapping.
- Tune the model using normalization.
- Implement the database application that includes:
- Fabricating substantial amount of data for the DA
- Getting started with the installed DBMS (Oracle/MySQL)---logging into and setting up the DBMS account, changing passwords, and so on.
- Implementing SQL commands to create, modify, query and maintain integrity on the installed DBMS for the DA.
- Build a user interface for the DA
- Design an advanced application using cursors, triggers, stored procedures
- Represent the application using NoSQL
|Types and/or Examples of Required Reading, Writing and Outside of Class Assignments - |
- Textbook assigned reading averaging 30 pages per week
- Reading on-line resources as directed by instructor though links pertinent to databases
- Reading library and reference material directed by instructor through course handouts
- Technical prose documentation, that supports and describes the database application assignments that are submitted for grades. The document would include the following aspects of the database application
- A description of the application, data requirements, and transaction requirements
- An entity-relationship diagram for the database application
- A set of relations with key attributes underlined for the DA obtained by translating E/R diagrams to relations
- A description on DA tuning to achieve the best possible performance using
- Tuning Indexes: a description of indexes implemented to improve the speed of your queries
- Tuning Conceptual Schema: a description of the normalization/denormalization process used on the DA schema
- Tuning Queries: a description of queries rewritten to improve the performance.
- A users manual for the DA