DBS216C Study Guide 2020 Updated 6 Feb - Copy PDF

Preview

Summary

ASSIGN...

Description

STUDY GUIDE

Databases II (DBS216C) Information Systems II (INL20DB) 2020

Compiled by: Mr. M. Motso enyane Mrs. M.I. Venter

Page

2

WORD OF WELCOME Welcome to Databases II. This study guide will guide you through this subject. It contains information about:  How you will be assessed.  The assessment tasks you will need to complete.  The additional resources you will need. LECTURER INFORMATION Lecturer: Office: Telephone: Consulting Hours: email:

Mrs MI Venter BHP 210 051 – 507 3092 Check on the timetable [email protected] COPYRIGHT ©

The copyright on this material is strictly reserved. Therefore the reproduction and photocopying of this study guide or parts thereof without obtaining the necessary permission, is prohibited. The distribution of the study guide or parts thereof can lead to a claim for damages and is furthermore a criminal offence.

Page

3

SYLLABUS  Database Systems II EXPANDED SYLLABUS-

1.

Database Systems 1.1.

Database Systems

1.2.

Data Models

1.3.

The Relational database model.

1.4.

Entity relationship modeling.

1.5.

Normalization of database tables.

1.6.

Structured Query Language (SQL) as a tool to get information from a Database.

Page

4

ASSESSMENT ANALYZES The final mark will be calculated as follow:

Percentage Allocation (Per Semester) Class Test + Class Assignments

Main Test Section A: Database Design Principles

Main Test Section B: SQL Programming

25%

40%

35%

Final Mark 100%

(Course Mark x 50%) + (Exam Mark x 50%) = FM Re-Assessment = Between 45 and 49

Verification of marks: Please note the following schedule for correction of Course Marks. No corrections will be made after the indicated dates: DATE

ITEM PRELIMINARY COURSE MARKS PUBLISHED (NOTICE BOARD) DEADLINE FOR STUDENTS COMPLAINTS FINAL COURSE MARKS PUBLISHED (NOTICE BOARD) PRELIMINARY COURSE MARKS PUBLISHED (NOTICE BOARD) DEADLINE FOR STUDENTS COMPLAINTS FINAL COURSE MARKS PUBLISHED (NOTICE BOARD)

GENERAL INFORMATION      

 

According to the educational regulations of the institution, assessment may be based on knowledge, insight, comprehension and ingenuity. Learners are responsible for ensuring that they are aware of and that they understand the Manual of Rules and Regulations for Evaluation: 2018. (See Calendar 2018). Please take note of the policies, rules and codes as stated in the yearbook of the institution (2018). Practical assignments must be handed in as stated by the lecturer. Late assignments will be penalized with 20% per day. Practical assignments copied by other students will not be accepted at all. All parties involved will receive zero (0). No excuses will be accepted, make sure that you keep your assignment save from other students. If the course facilitator is not available in the office during consulting time, go to Departmental Manager in K5 for enquiries. If the course facilitator is not in time for classes, the following rules apply : o Students have to wait 10 minutes at the class. o One representative must go to the Departmental Manager of IT after 10 minutes in K5. If the course facilitator has made arrangements with the secretary, it will be given to the representative. If you did not write a test, a doctor’s letter must be handed in within 72 hours. Without a doctor’s letter, no re-test will be granted. No re-test will be given if you already wrote a test.

Page   

   

5

Only one (1) re-test will be available for each test. The re-test will be at the end of each semester. The retest will be on all the work done in the semester. Assessment papers will only be available in English. Practical assessments on disk: When you are finished, you must not close the application. The candidate must give the disk to the supervisor to check if the candidate has saved the test on the disk. If the candidate did not save the test on the disk, the supervisor will save the test on the student’s disk and 20% will be deducted from student’s work. If the student has closed the application and the test is not saved on the disk, the student will get 0% for the test. The lecturer also has the right to ask questions in the tests about work that was discussed in class but does not necessarily appear in the textbooks. It is important to attend all the theory and practical classes. No information will be repeated for students that did not attend the classes. All students must check the IT notice boards on the Block I building as well as e-Thuto at least twice a week for new information. The lecturer has the right to make any adjustments to the time schedule if necessary. All changes will be announced in class.

INTERNET To log onto e-Thuto: www.cut.ac.za Current Student e-Thuto for Web CT Your user name and password will be your student number. Additional material will be placed on e-Thuto for students. The complete study Guide will be available on e-Thuto. The schedule for 2018 and announcements will also be put on e-Thuto.

RESOURCES Prescribed Resource:

Page

6

Database Design - 2nd Edition ADRIENNE WATT NELSON ENG

This open source text book will be available on E-Thuto for you to download. About the Book Database Design – 2nd Edition is a remix and adaptation based on Adrienne Watt’s book Database Design. Works that are part of the remix for this book are listed at the end of each chapter. For information about what was changed in this adaptation, refer to the copyright statement in the copyright section or on the webbook homepage. BCcampus Open Education began in 2012 as the B.C. Open Textbook Project with the goal of making post-secondary education in British Columbia more accessible by reducing student costs through the use of openly licenced textbooks and other OER. BCcampus supports the post-secondary institutions of British Columbia as they adapt and evolve their teaching and learning practices to enable powerful learning opportunities for the students of B.C. BCcampus Open Education is funded by the British Columbia Ministry of Advanced Education, Skills & Training, and the Hewlett Foundation. Open textbooks are open educational resources (OER) created and shared in ways so that more people have access to them. This is a different model than traditionally copyrighted materials. OER are defined as teaching, learning, and research resources that reside in the public domain or have been released under an intellectual property license that permits their free use and re-purposing by others.1 Our open textbooks are openly licensed using a Creative Commons licence, and are offered in various e-book formats free of charge, or as printed books that are available at cost. For more information about this project, please see the BCcampus Open Education website.

Additional Resources: Slides, Video Lessons and Additional Notes will be placed on E-Thuto for students and will form a very important component of this subject.

Supplementary Resource: Students are also encouraged to use the following text book as a supplementary resource throughout the course. This is not an open source textbook and student will have to buy this book.

Database Principles: Fundamentals of Design, Implementation and Management Second Edition. 2013 Author: Rob & Coronel. ISBN:

9781408066362

Page

7

SPECIFICATIONS OF UNITS STUDY UNIT 1 Text Book: Database Systems – Design, Implementation, Management. Database Concepts: Database Systems and Data Models Outcomes At the end of this unit, you should be able to describe:  What a database is, what it does, and why database design is important.  How modern databases evolved from files and file systems.  Flaws in file system for data management.  What a DBMS is, what it does, and how it fits into the database system.  Why database models are important  The basic building blocks for a data model  Business rules and how they effect database design  How major data models evolved,  Advantages and disadvantages of each model  How data models can be classified by level of abstraction.

Sources Will be placed on E-Thuto

E-Learning Additional material will be placed on E-Thuto.

Learner support Facilitation, Consultations, E-Learning, peer support.

Self-Assessment Will be placed on E-Thuto

Page

8

STUDY UNIT 2 The Relational Database Model Outcomes At the end of this unit, you should be able to:  Describe the characteristics of a Relational table.  Define and describe the following: o Functional dependencies, o A determinant, o A Relational Schema and its components, o The integrity rules, o Left and Right Outer.  Define and describe and recognise the following: o Super key, Candidate key, Primary key, Secondary key, Foreign key,  Describe the relational model’s basic components namely: entities, attributes, and relationships among entities.  Organize entities and their attributes into tables.  How relational database operators, data dictionary, and the system catalogue works.  Handle data redundancy in the relational database model.  Explain the importance of indexing.

Sources Will be placed on E-Thuto

E-Learning Additional material will be placed on E-Thuto.

Learner support Facilitation, Consultations, E-Learning, peer support.

Self-Assessment Will be placed on E-Thuto

STUDY UNIT 3 Entity Relationship(E- R) Modelling Outcomes At the end of this unit, you should be able to:  Discuss the purpose of a conceptual model.  Define modelling concepts like: Entities, relationships, attributes, identifiers, composite keys, derived attributes, connectivity, cardinality, existence dependence, relationship strengths, relationship participation, relationship categories,  Represent 1:1, 1:M, M:M and M:M:M relationships in an ER Model and in a Relational Database.  Distinguish between the purpose of internal and external models in the design process.  Define relationships between entities and implement such relationships into the database design process.  Create an ER Model for business problems, using all the component of an ER Diagram.  Interpret ER models, (creating business rules from ER Models)

Sources Will be placed on E-Thuto

E-Learning Additional material will be placed on E-Thuto.

Page

9

Assignment Assignment will be given in class. Reason for the assessment: Formative assessment. The cognitive, and psychomotor level of the students on ER Modelling will be evaluated. Learners will have to apply the knowledge attained in this learning unit to the case studies to design effective Databases using ER Models. Outcomes: Cognitive performance  Identify the business rules for the industry.  Convert effectively business rules into functional dependencies.  Design a normalized relational database using normalization and ERD’s as methodologies. Psychomotor performance  Demonstrate effective communication skills both electronically and on paper.  Present the design by means of a PowerPoint presentation. Affective performance  Work effectively with others as a member of a team, group, and organisation in which the case study is investigated.

Learner support Facilitation, Consultations, E-Learning, peer support.

Self-Assessment Will be placed on E-Thuto

STUDY UNIT 4 Introduction to Structured Query Language (SQL)

Khan Academy SQL Section 1 – 4 Outcomes At the end of this unit, you should be able to use SQL:  For data manipulation (to add, modify, delete, and retrieve data in a database)  To query a database to extract useful information.  For data administration (to create tables, indexes, and views).  For the joining of tables.  To update a database.

Sources Khan Academy Section 1,2,3,4 and Chapter 8

Layout of SQL Khan Academy Sections: Section 1: SQL basics In this section you will learn the basics of creating tables and selecting data in various different ways. 

Welcome to SQL



Creating a table and inserting data



Challenge: Book list database

Page 

Querying the table



Challenge: Box office hits database



Aggregating data



Challenge: TODO list database stats



S-Q-L or SEQUEL?



Project: Design a store database

10

Section 2: More advanced SQL queries In this section you will learn how to perform more advanced SQL queries using AND/OR, IN, LIKE, HAVING, and more. 

More complex queries with AND/OR



Challenge: Karaoke song selector



Querying IN subqueries



Challenge: Playlist maker



Restricting grouped results with HAVING



Challenge: The wordiest author



Who issues SQL queries?



Calculating results with CASE



Challenge: Gradebook



Project: Data dig

Section 3: Relational queries in SQL In this section you will learn how to store related data in multiple tables and use joins to bring them together (inner joins, outer joins, and self joins). 

Splitting data into related tables

Page

11



JOINing related tables



Challenge: Bobby's Hobbies



Joining related tables with left outer joins



Challenge: Customer's orders



Joining tables to themselves with self-joins



Challenge: Sequels in SQL



Combining multiple joins



Challenge: FriendBook



Project: Famous people



More efficient SQL with query planning and optimization

Section 4: Modifying databases with SQL In this section you will learn how to update data, delete data, and change table schemas with SQL commands UPDATE, DELETE, ALTER, and DROP. 

Using SQL to update a database



Changing rows with UPDATE and DELETE



Challenge: Dynamic Documents



Altering tables after creation



Challenge: Clothing alterations



Make your SQL safer



Project: App impersonator

E-Learning Additional material will be placed on E-Thuto (Web-CT).

Page

12

Assignment Assignments will be given in theory classes.

Learner support Facilitation, Consultations, E-Learning, peer support.

Self-Assessment Review Questions and Problems will be provided

STUDY UNIT 5 Introduction to Normalization of Database Tables (Notes will be provided) Outcomes At the end of this unit, you should be able to:  Describe what Normalization is and what role it plays in database design process.  Define the 1NF, 2NF, 3NF 4NF,  Identify business rules and transform it into dependency diagrams,  Draw up dependency diagrams,  Transform entities to the required normal form,  Use ERD and normalization concurrently to produce a good database design,

Sources Notes will be provided

E-Learning Additional material will be placed on E-Thuto (Web-CT).

Learner support Facilitation, Consultations, E-Learning, peer support.

Self-Assessment Review Questions and Problems will be provided

Page

13

TIME SCHEDULE Please note that this time schedule is subjected to change. Please check E-thuto on a daily basis for any announcements regarding changes to the schedule! Period

Date

Unit or Syllabus covered

Week 1

3 Feb to 7 Feb

Unit 1 - Getting Started with Databases

Week 2

10 Feb to 14 Feb

Unit 1 - Getting Started with Databases

Week 3

17 Feb to 21 Feb

Class Test 1 - 17 & 19 Feb (Scope Unit1) Unit 2 - The Relational Model

Week 4

24 Feb to 28 Feb

Unit 3 - Data Modelling and the ER Model

Week 5

2 March to 6 March

Class Test 2 – 2 & 4 March (Scope Unit 2) Unit 3 - Data Modelling and the ER Model

Week 6

9 March to 13 March

Unit 4 - SQL Khan Academy (Section 1)

Week 7

16 March to 20 March

Class Test 3 – 16 & 18 March (Scope Unit 3) Unit 4 - SQL Khan Academy (Section 2)

Week 8

23 March to 27 March

Holiday

Week 9

30 March to 31 March 1 April to 3 April

Week 10

6 April to 8 April

Week 11

14 April to 17 April

Test Revision Test Week Start Main Test: Section A and B will be written in the same session during the main test week. Section A: (40% of course mark)  Unit 2 - The Relational Model  Unit 3 - Data Modelling and the ER Model) Section B: (35% of course mark)  Unit 4 – SQL Section 1 and 2 Unit 4 - SQL Khan Academy (Section 3) Supplementary Test

Week 12

20 April to 24 April

Unit 5 Introduction to Normalisation

Week 13

28 April to 30 April

-

Week 14

4 May to 8 May

Unit 4 - SQL Khan Academy (Section 4) 8 May Final Marks on ITS

Week 15

11 May to 15 May

Unit 5 Introduction to Normalisation

Week 16

18 May to 22 May

Unit 5 Introduction to Normalisation

25 May to 19 June

Exam

22 June to 26 June

Supplementary Exam

Page

14

DISCIPLINARY RULES FOR STUDENTS

6.1.1

Disciplinary authority (a)

The Central University of Technology, Free State (CUT) Council is the highest disciplinary authority at the University.

(b)

By virtue of the Higher Education Act (Act No. 101 of 1997), as amended, the general supervision and control of student discipline is the responsibility of the ViceChancellor and Principal.

(c)

The Vice-Chancellor and Principal, by virtue of the powers vested in him/her, may request any staff member to assist in the execution of his/her duties, including the maintenance of discipline and order at CUT. All bodies and officials with powers of adjudication will thus be nominated and appointed by the Vice-Chancellor and Principal, and will be directly accountable to him/her in the execution of their duties. Notwithstanding any stipulation in the disciplinary rules, the Vice-Chancellor and Principal may revise any disciplinary proceedings mero motu (i.e. by motion of his/her own free will).

(d)

In all cases where information on a student’s ailment, condition or problem should not be made generally known, is in fact made known to the registered psychologists at the Wellness Centre, or comes to their knowledge, such registered psychologists will be entitled to reveal the information to the Vice-Chancellor and Principal, in accordance with authorisation by virtue of the Medical, Dental and Supplementary Health Professio...