Through this Scholar Course, students develop skills and knowledge relating to the innerworking of databases, Microsoft Access, Entity Relationship Diagrams, and Big Data. They learn to use SQL programming language to structure and design databases for _________
Data, Databases, Relationship Model, Access, MySQL
COURSE OVERVIEW
A study of database models (including relational, hierarchical, and networks), normalization techniques, query languages and entity-relationship theory, LSIS 5451 is an introduction to databases. No prior knowledge of database concepts or use is assumed or required. The purpose of these modules is to teach you the essential relational database concepts, technology, and techniques that you need to begin a career as a database developer. These modules do not teach everything of importance in relational database technology, but it will give you sufficient background to be able to create your own personal databases and to participate as a member of a team in the development of larger, more complicated databases. You will also be able to ask the right questions to learn more on your own.
This course is part of the Information Science cirriculum at North Carolina Central University. As an Information Technology Specilaist for over 20 years and a graduate of the IS program at NCCU, I have extensive experience in providing practical, real-world case scenarios for review and practice throughout. Please note that this course is asynchronus. Lectures will be posted weekly for review. The course uses mutliple forms of pedagogy for instruction:
***Please note that this course is designed with several videos in each lesson. However, the only required video to watch will be the weekly lecture, which will be uploaded on the same day each week (TBD).***
_____________________________________________________________________________
STUDENTS LEARNING OUTCOMES
Upon completing the course LSIS 5451, students will be able to:
1. Explain fundamental concepts in databases and database design
2. Create, modify and query Microsoft Access databases.
3. Demonstrate the ability to create, modify and query MySQL databases.
4. Design, create and demonstrate a database.
5. Create forms and publish an online database and web interface as part of a final project.
6. Exhibit skills in the areas of web-based database design and implementation principles, including the installations and use of database-driven website design technologies such as XAMP and PHP.
_____________________________________________________________________________
REQUIRED TECHNOLOGY
You must have the following installed and operational by the first class session:
Hardware: A computer with Internet access
Software: Microsoft Access (Available via NCCU Student ITS site - https://nccu.teamdynamix.com/TDClient/1852/Portal/KB/ArticleDet?ID=93664)
Software: XAMPP (Available from http://apachefriends.org)
Software: A browser (Firefox, Chrome, Microsoft Edge)
_____________________________________________________________________________
WEEKLY ASSIGNMENTS
Each week, students will review the objectives, videos (only the lecture is required), and complete the activties (Comment and Update).
_____________________________________________________________________________
FINAL PROJECT
As part of the course, there will be a final project. The project rubric and information will be released in Lesson 1 and you will be assigned to a group to complete. The project will be due at the end of Lesson 6.
Lesson 1 Outcomes:
■ Understand the importance of databases in Internet Web applications and mobile apps
■ Understand the nature and characteristics of databases
■ Understand the potential problems with lists
■ Understand the reasons for using a database
■ Understand how using related tables helps you avoid the problems of using lists
■ Know the components of a database system
■ Learn the elements of a database
■ Learn the purpose of a database management system (DBMS)
_____________________________________________________________________________
What is a database?
Media Source: (Guru99, 2013)
What is the difference between a spreadsheet and a database?
Media Source: (365 Data Science, 2017)
Learning Resources:
Key Terms:
Practice Tutorial:
Try it for yourself!
Click on this link to obtain the Access file to complete this practice exercise. The video below will walk you step by step in how to complete each step.
Access File: https://drive.google.com/file/d/1U2NSPPV_6N6YAh3nViBpQMwTJevg-ie2/view?Commentusp=sharing
Media Source: (DeSimone-Shabrack, Database Concepts Chapter 1 Access Workbench, 2019)
_____________________________________________________________________________
Comment: Why do you think the study of database technology is important? Make sure to comment on at least two other student posts in the discussion forum.
_____________________________________________________________________________
Make an update: Record yourself completing one step of the practice example above and post to the community (can use your own data). Then watch and comment on another student's video. What did they do differently? What did you learn from them?
DISTRIBUTE PRE-COURSE SURVEY BEFORE START OF COURSE
The survey is to gauge expereince of the learner in database design.
_____________________________________________________________________________
***Lecture is to be pre-recorded an posted in the Student view each week on the assigned class day***
Performance Objective:
Upon completion of this lesson, the student will be able to create a database by entering data into a table orform.
Specific Objectives:
Define terms associated with the lesson
Teaching Suggestion:
Resources:
_____________________________________________________________________________
***RELEASE RUBRIC, PROJECT OUTLINE AND GROUP ASSIGNMENTS***
_____________________________________________________________________________
AACSB International – The Association to Advance Collegiate Schools of Business
Standards: AACSB: Information Technology
Lesson 2 Outcomes:
■ Learn the conceptual foundation of the relational model
■ Understand how relations differ from nonrelational tables
■ Learn basic relational terminology
■ Learn the meaning and importance of keys, foreign keys, and related terminology
■ Understand how foreign keys represent relationships
■ Learn the purpose and use of surrogate keys
■ Learn the meaning of functional dependencies
■ Learn to apply a process for normalizing relations
_____________________________________________________________________________
What is a database relationship model?
Media Source: (Prescott Computer Guy, 2011)
What are Entity Relationship Diagrams and how do we design them?
Media Source:(Lucid Chart, 2017)
What are all these keys about?
Media source: (Edu Therapy, 2016)
Learning Resources:
Practice Tutorial:
Try it for yourself!
Click on the links below to obtain the two Access files to complete this practice exercise. The video below will walk you step by step in how to complete each step.
Access Files: https://drive.google.com/file/d/1uhL_chLOGkEPbdp1YIkdFXgIiBLTfqKd/view?usp=sharing
https://drive.google.com/file/d/1lc70Wmpq_JtpHdqg5avKtxccXUdR_gLE/view?usp=sharing
Media Source: (DeSimone-Shabrack, Database Concepts Chapter 2 Access Workbench, 2019)
_____________________________________________________________________________
Comment: Why is a relational model important? Make sure to comment on at least two other student posts in the discussion forum.
_____________________________________________________________________________
Make an update: Record yourself completing a partial ERD (two tables max). Then watch and comment on another student's video. What did they do differently? What did you learn from them?
***Lecture is to be pre-recorded an posted in the Student view each week on the assigned class day***
Performance Objective:
Upon completion of this lesson, the student will be able identify all types of keys, their use, and how to diagram tables and relationships.
Specific Objectives:
Teaching Suggestions:
Resources:
_____________________________________________________________________________
AACSB International – The Association to Advance Collegiate Schools of Business
Standards: AACSB: Information Technology
Lesson 3 Outcomes:
■ Learn basic SQL statements for creating database structures
■ Learn basic SQL statements for adding data to a database
■ Learn basic SQL SELECT statements and options for processing a single table
■ Learn basic SQL SELECT statements for processing multiple tables with subqueries
_____________________________________________________________________________
What is SQL?
Media Source: (The', 2019)
How to write SQL statements in Microsoft Access:
Media Source: (Magic Monk, 2016)
How to use SQL to run a query in MS Access:
Media Source: (Geography @ MCC, 2017)
Learning Resources:
Practice Tutorial:
Try it for yourself!
Click on the link below to obtain the Access file to complete this practice exercise. The video below will walk you step by step in how to complete each step.
Access Files:https://drive.google.com/file/d/10hqS1gSvHx6ja1r79SSwMUV0WyFzpiAj/view?usp=sharing
Media Source: (DeSimone-Shabrack, Database Concepts Ch. 3 Access Workbench, 2019)
_____________________________________________________________________________
Comment: What does SQL stand for and why is it important to learn it? Make sure to comment on at least two other student posts in the discussion forum.
_____________________________________________________________________________
Make an update: Record yourself completing one step of the practice example above and post to the community (can use your own data). Then watch and comment on another student's video. What did they do differently? What did you learn from them?
***Lecture is to be pre-recorded an posted in the Student view each week on the assigned class day***
Performance Objective:
Introduction to the SQL language in Access to create tables, structure and queries.
Specific Objectives:
Teaching Suggestions:
The goal of this lesson is to prepare students to use SQL for database designing and coding. This initial exposure will help build stills that will be necessary to use in more advanced database building with other system. While MS Access will allow for some SQL statements to be directly coded, there are a few key points for students to remember:
Resources:
_____________________________________________________________________________
AACSB International – The Association to Advance Collegiate Schools of Business
Standards: AACSB: Information Technology
Lesson 4 Outcomes:
■ Learn the basic steps of systems analysis and design
■ Learn the basic stages of database development
■ Understand the purpose and role of a data model
■ Know the principal components of the E-R data model
■ Understand how to interpret traditional E-R diagrams
■ Understand how to interpret the Information Engineering (IE) model’s Crow’s Foot E-R diagrams
■ Learn to construct E-R diagrams
■ Know how to represent 1:1, 1: N, N:M, and binary relationships with the E-R model
_____________________________________________________________________________
ERD Diagrams further explained:
Media Source: (Lucid Chart, 2017)
How to Normalize data:
Media source: (channel5567, 2015)
Using Visio to create ERD’s with Crow’s Foot Notation:
Media source: (Green, 2016)
Learning Resources:
Practice Tutorial:
Try it for yourself!
Click on the link below to obtain the Access file to complete this practice exercise. The video below will walk you step by step in how to complete each step.
Access Files:https://drive.google.com/file/d/1tsiCyCk0GM2O4Eu18l0RUEwDVIXk_hid/view?usp=sharing
Media source: (DeSimone-Shabrack, Database Concepts Ch. 4 Access Workbench, 2019)
_____________________________________________________________________________
Comment: Name the three stages in the process of developing database systems and summarize the tasks in each. Make sure to comment on at least two other student posts in the discussion forum.
_____________________________________________________________________________
Make an update: Record yourself completing a Full ERD for the practice example above. Then watch and comment on another student's video. What did they do differently? What did you learn from them?
***Lecture is to be pre-recorded an posted in the Student view each week on the assigned class day***
Performance Objective:
Students will learn the basics of system analysis and design and the role of ERD diagrams.
Specific Objectives:
Teaching Suggestions:
____________________________________________________________________________
AACSB International – The Association to Advance Collegiate Schools of Business
Standards: AACSB: Information Technology
Lesson 5 Outcomes:
■ Learn how to transform E-R data models into relational designs
■ Practice applying the normalization process
■ Understand the need for denormalization
■ Learn how to represent weak entities with the relational mode
_____________________________________________________________________________
Normalizing Data:
Media Source: (Curbal, 2020)
Normalizing vs. Denormalizing:
Media source: (Software Architecture Matters, 2019)
Weak vs. Strong Entities:
Media source: (Jain, 2017)
Learning Resources:
Practice Tutorial:
Try it for yourself!
Click on the links below to obtain the three Access files to complete this practice exercise. The video below will walk you step by step in how to complete each step.
Access Files:https://drive.google.com/file/d/16pjpVirmYgsC1LgWHJmfYFI45EmaJQsR/view?usp=sharing
https://drive.google.com/file/d/1jgGNvRTil4WcTSH5BpW23XmNdlWA8Eix/view?usp=sharing
http://drive.google.com/file/d/1xoNCY2zrWa9qsmSYrZhvkid8Nm7A-XuT/view?usp=sharing
Media source: (DeSimone-Shabrack, Database Concepts Ch. 5 Access Workbench, 2019)
_____________________________________________________________________________
Comment: When is denormalization justified? Make sure to comment on at least two other student posts in the discussion forum.
_____________________________________________________________________________
Make an update: Record yourself completing one step of the practice example above and post to the community (can use your own data). Then watch and comment on another student's video. What did they do differently? What did you learn from them?
***Lecture is to be pre-recorded an posted in the Student view each week on the assigned class day***
Performance Objective:
Students will be able to develop E-R data models with normalized and denormalized data, and be able to identify strong and weak entities/
Specific Objectives:
Teaching Suggestions:
Resources:
_____________________________________________________________________________
AACSB International – The Association to Advance Collegiate Schools of Business
Standards: AACSB: Information Technology
Lesson 6 Outcomes:
■ Understand the need for and importance of database administration
■ Know basic administrative and managerial DBA functions
■ Understand the need for concurrency control, security, and backup and recovery
■ Learn about typical problems that can occur when multiple users process a database concurrently
_____________________________________________________________________________
What is a database administrator?
Media source: (Technology Profession, 2015)
What is concurrency control?
Media source: CMU Database Group, 2018)
Learning Resources:
Practice Tutorial:
Try it for yourself!
Click on the links below to obtain the three Access files to complete this practice exercise. The video below will walk you step by step in how to complete each step.
Access Files:https://drive.google.com/file/d/11r9xHgqidEvqPxSt0lQEPtfah8gRTfC6/view?usp=sharing
https://drive.google.com/file/d/1hkDv6z7XtQ9OTqXkK6qZBnxtiqodg2b1/view?usp=sharing
https://drive.google.com/file/d/1o-urwP1851Qy79gAsnson8bi4mfKKyfd/view?usp=sharing
Media source: (DeSimone-Shabrack, Database Concepts Access Workbench Chapter 6, 2019)
_____________________________________________________________________________
Comment: Explain how database administration tasks vary with the size and complexity of the database. Make sure to comment on at least two other student posts in the discussion forum.
_____________________________________________________________________________
Make an update: Record yourself completing one step of the practice example above and post to the community (can use your own data). Then watch and comment on another student's video. What did they do differently? What did you learn from them?
_____________________________________________________________________________
FINAL PROJECTS ARE DUE:
Groups will submit their final projects to the link provided (start of Lesson 6). Each group will need to peer review another groups submission and present them with findings.
DISTRIBUTE LINK FOR FINAL PROJECT TO STUDENTS FOR SUBMISSION
_____________________________________________________________________________
***Lecture is to be pre-recorded an posted in the Student view each week on the assigned class day***
Performance Objective:
The goal of this lesson with regard to backup and recovery is to raise the students’ consciousness to these issues. They should get a general idea of the importance of backup and recovery and the general nature of backup and recovery tools and techniques.
Specific Objectives:
Teaching Suggestions:
Resources:
_____________________________________________________________________________
AACSB International – The Association to Advance Collegiate Schools of Business
Standards: AACSB: Information Technology
365 Data Science. (2017, Nov. 3). Database vs Spreadsheet - Advantages and Disadvantages. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=x4Xt0M1mHbc&feature=youtu.be
channel5567. (2015, Aug 14). Normalization - 1NF, 2NF, 3NF and 4NF. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=UrYLYV7WSHM
CMU Database Group. (2018, Oct 31). CMU Database Systems - 16 Concurrency Control Theory (Fall 2018). Retrieved from www.youtube.com: https://www.youtube.com/watch?v=r0nI_yV9KCo
Curbal. (2020, Mar 25). Normalizing data: The what, why and how. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=5YGgqzQadwM
DeSimone-Shabrack, A. K. (2019, Nov 3). Database Concepts Access Workbench Chapter 6. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=ZFtawkqGTNw
DeSimone-Shabrack, A. K. (2019, Sept 14). Database Concepts Ch. 3 Access Workbench. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=Xg4TNgBJJOc&feature=youtu.be
DeSimone-Shabrack, A. K. (2019, Sept 29). Database Concepts Ch. 4 Access Workbench. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=nyx0HGQTVAg&t=2s
DeSimone-Shabrack, A. K. (2019, Oct 4). Database Concepts Ch. 5 Access Workbench. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=tCIh7tuYM0w
DeSimone-Shabrack, A. K. (2019, Sept. 2). Database Concepts Chapter 1 Access Workbench. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=lW946YsYFIc&feature=youtu.be
DeSimone-Shabrack, A. K. (2019, Sept. 6). Database Concepts Chapter 2 Access Workbench. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=_7ImcBLcDAk
Edu Therapy. (2016, Mar 12). Candidate Key in DBMS (Candidate key, Primary Key, Foreign Key, Alternate Key, Super Key). Retrieved from www.youtube.com: https://www.youtube.com/watch?v=aSXdGPGcELo
Geography @ MCC. (2017, Feb 4). Creating a Query In SQL View (MS Access). Retrieved from www.youtube.com: https://www.youtube.com/watch?v=dl8OT8HWE2I&feature=youtu.be
Green, B. (2016, June 2016). Visio 2013 - Database Diagram (Crows Foot Notation). Retrieved from www.youtube.com: https://www.youtube.com/watch?v=xzQQW0NiAMM
Guru99. (2013, Jul 12). What is Database & SQL? Retrieved from www.youtube.com: https://www.youtube.com/watch?v=FR4QIeZaPeM&feature=youtu.be
Jain, S. (2017, Jun 29). Strong entity vs weak entity in dbms. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=45BdZJ2_SZo
Kroenke, D. M., Auer, D. J., Vandenberg, S. L., & Yoder, R. C. (2017). Database Concepts (8th Ed.). Hoboken: Pearson Education, Inc. .
Lucid Chart. (2017, Mar 6). Entity Relationship Diagram (ERD) Tutorial - Part 1. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=QpdhBUYk7Kk&feature=youtu.be
Lucid Chart. (2017, Jul 14). Entity Relationship Diagram (ERD) Tutorial - Part 2. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=-CuY5ADwn24
Magic Monk. (2016, Mar 5). SQL with Microsoft Access 2016 lesson 1 - Create table. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=x3G4WeaIwJw&feature=youtu.be
Prescott Computer Guy. (2011, Sept. 30). Relational Database Concepts. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=NvrpuBAMddw&feature=youtu.be
Software Architecture Matters. (2019, Jan 2019). Normalization vs. Denormalization. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=GIIIZ2R1gPc
Technology Profession. (2015, Sept 10). Database Administrator. Retrieved from www.youtube.com: https://youtu.be/v65-W80mjII
The', D. (2019, April 16). What is SQL? [in 4 minutes for beginners]. Retrieved from www.youtube.com: https://www.youtube.com/watch?v=27axs9dO7AE&feature=youtu.be