COM106 Introduction to Databases Module Overview PDF

COM106: Introduction to Databases Introduction and Module Overview Timetable and Attendance 2 So …… What’s this Lecture About? See the Module Handbook on BlackBoard for more informat...

COM106: Introduction to Databases Introduction and Module Overview Timetable and Attendance 2 So …… What’s this Lecture About? See the Module Handbook on BlackBoard for more information Lectures When & Where – Timetable Monday 15:15 – 17:05 BC-LG-211 Lab Classes Part-Time Group A: Monday 17:15 – 19:05 BC-02-303. BC-03-303 Full-Time IC, CS, SE: Friday 09:15 – 12:05 BC-02-303, BC-03-303, BC-03-305, BC-03-311 Full-Time Group CT: Friday 12:15 – 15:05 BC-03-305 So …… What’s this Lecture About? Who - Teaching Staff Dr. Matthew Burns Dr. Hanif Ullah Module Coordinator for Module Coordinator for Full Time Part Time Email: [email protected] Email: [email protected] Dr. Naveed Khan– Lectures & Labs Email: n.khan @ulster.ac.uk Dr. Jose Santos – Labs Email: ja.santos @ulster.ac.uk So …. Why are we learning about Databases? First - What is ‘data’? What is a ‘database’? Dictionary Definition:- A database is an organised facts and statistics collected collection of data. together for reference or analysis. Information is held such that it can Computer data is information easily be accessed, managed, and processed or stored by a computer. updated. May be in a variety of forms from bits and bytes to text documents, images, audio clips, software programs, or other types of data. So…. Why are we learning about Databases? Data – Information – Knowledge - Wisdom Knowledge enables understanding or wisdom necessary for effective decision making. Knowledge is information placed in context based on facts and meaning – it is actionable. Information is data with relevance and purpose. It ‘informs’ Data are discrete objective facts that have no meaning in isolation  Databases are of fundamental importance in a digital world. So…. Why are Necessary in any situation where:- information (organised data) is needed …… we learning to build knowledge …… about and make decisions about future actions (wisdom) Databases? Databases are also of fundamental importance in Computing and Business, and something all Computing/Business Professionals need to know about.  There’s an unimaginably large amount of data So…. Why are in the digital universe : estimated at 16 Zettabytes (1021 Bytes) in we learning 2017, 90% of which was created in the last two years. For more info, and some truly about staggering stats, google ‘amount of Databases? data in the world 2024’ Increasing need to analyse data in near real-time to derive value from it Look up : Data Analytics Big Data Data Mining Zettabyte What will I be able to do at the end of this module? A successful student will be able to: Explain the concepts underlying the design, structure, implementation, manipulation and management of relational databases in modern information systems. Apply appropriate models, processes and techniques in the design, implementation and use of database systems to meet an outline business scenario. Construct a database from a given relational schema using a commercial database management system. Perform basic data manipulation and information retrieval operations using SQL. You will have: an understanding of the theoretical basis, structure, management and workings of database systems and provide some insight into recent developments and future trends a knowledge and understanding of the techniques of database design and the ability to apply these techniques to implement such designs on a relational database management system an understanding and practical experience of data manipulation and query in SQL experience in using a commercial database management system. What will we be Looking at in the Module?:- Database Basics Data Models Query Processing in the Relational Model Data Management Issues Running SQL in Jupyter Notebooks Combining SQL and Python What will we be Looking at in the Module?:- We’ll be using Microsoft SQL Server 2017 or Azure SQL Databases as the main Database Management Systems (DBMS) in the labs. We’ll also introduce Jupyter Notebooks.  How Do YOU Learn the Material Lots of material and support available:- Lectures & Practicals – attendance is compulsory and engagement is monitored ‘Homeworks’ – every week, in preparation for the next week. Blackboard Learn: https://learning.ulster.ac.uk Library – books suitable for an Introductory Database Course Lots & Lots of external on-line resources available:- - books, blogs, videos, entire courses, simulators, etc. - we’ll be using some and recommending others This module requires 200 hours total effort (at least) Lectures - 24 hrs Lab - 24-36 hrs Independent Study - 140-152 hrs. Plan to allocate between ten to twelve hours for private study per week.  How – Books - Library Any introductory text on Databases will cover the material in this module. There are lots and lots available – both hardcopy and electronic Check out the library catalogue available through the portal or directly at http://catalogue.library.ulster.ac.uk/ And the Subject guide for Computing (access through the portal) or at http://guides.library.ulster.ac.uk/computing There are a number of recommended text books for the module. It’s not essential that you buy one, but it IS ESSENTIAL that you regularly use one! Be aware - the slides won’t give all the information you’ll need for the module. Database systems: a practical approach to design, implementation, and management Hard copy available in the library Connolly, Thomas M; Begg, Carolyn E Also available as an ebook Pearson Education, 6th ed., Global ed. 2015 ISBN: 1292061189, 9781292061184 Earlier editions are also available Shelfmark: 005.74/CON  How – Books - Library There are a number of recommended text books for the module. It’s not essential that you buy one, but it IS ESSENTIAL that you regularly use one! Be aware - the slides won’t give all the information you’ll need for the module. Database systems: a practical approach to Hard copy available in the library design, implementation, and management Connolly, Thomas M; Begg, Carolyn E Also available as an ebook Pearson Education, 6th ed., Global ed. 2015 Earlier editions are also available ISBN: 1292061189, 9781292061184 Shelfmark: 005.74/CON These are the main text books More details are available that will be used and referenced in the Additional Resources Folder throughout the module Database administration fundamentals, exam 98-364 An electronic copy is available on Gambrel, Bryan; Microsoft Corporation Blackboard (Additional Resources Folder) Wiley, 2011 ISBN: 0470889160, 9780470889169 Shelfmark: 005.74/MIC How – Books - Library Database Design for Mere Mortals®: A Hands- on Guide to Relational Database Design Available in the library Michael J. Hernandez as an ebook Addison-Wesley Professional, 3rd ed. 2013 ISBN: 0-321-88449-3, 0-13-312228-X SQL Queries for Mere Mortals®: A Hands-On Guide to Data Manipulation in SQL Available in the library John Viescas; Michael J. Hernandez as an ebook Addison-Wesley Professional, 3rd ed. 2014 ISBN: 0-321-99247-4, 0-13-382488-8 How – On-line Resources Lots and Lots of external on-line resources available:- - books, blogs, videos, entire courses, simulators, etc. - we’ll be using some and recommending others Easily found – search Google, YouTube, etc As we go through the course, I’ll be posting the resources we recommend in the Additional Resources Folder in Blackboard. If you come across something new you think would be of benefit to other students - let me know and I’ll put it up. How – Get Your Own Copy of SQL Server 2017 Microsoft SQL Server 2017 Express Edition - the main relational database management system used throughout this module Download and Install instructions are available in the Lab Resources Folder on Blackboard  What You can Expect of Us What We Expect of You Lots of learning material – easily Engage – as an Independent Learner accessible Attend classes – on time Interactive classes Contribute Answers to your questions - ask Ask Questions Monitoring your engagement Complete all coursework – EC1 Help when you need it – ask Use the available resources as open door – but preferably email for directed and in your own independent an appointment study Prompt feedback We are here to facilitate YOUR learning  How is the Module Assessed? 100% Coursework - Two pieces; Set Exercise and Assignment Assignment (60%) - Given an outline business scenario and an unstructured file of sample data, design and implement a database capable of meeting the business need. Submission, by 12:00 noon Wednesday 11th December, of: A video screencast showing a walkthrough demonstration of the code and its execution against a set of given test cases. Set Exercise (40%) - A 90 min class test, held during allocated labs scheduled in Week 7 (Week Commencing Monday 4th November 2024). Individual Assessments. No Group Work How is the Module Assessed? 40% or more aggregated To Pass – over both coursework components. It is not necessary to pass each component separately. Practical Exam Assignment Module (40%) (60%) Mark Pass/Fail 60% 50% 54% Pass 75% 25% 45% Pass 10% 55% 43% Pass 39% 40% 41% Pass 40% 38% 39% Fail 80% 10% 38% Fail If you miss an assessment (for valid reasons) – submit an EC1 If you FAIL - Resit failed components in the next year (capped at 40%) Introduction to Databases and the Relational Model See Chapter 1, 2 & 4 of Connolly & Begg (6th ed) (or similar chapters in other textbooks.) Data – Information – Knowledge - Wisdom Decisions – based on Knowledge - --- based on Data Knowledge enables understanding or wisdom necessary for effective decision making. Extracted from Related Data Knowledge is information placed in context based on facts and meaning – it is actionable. Related Data Information is data with relevance and purpose. It ‘informs’ Disorganised Data are discrete objective facts that have no meaning in isolation 26 Introduction to Databases & the Relational Model So, whilst DATA is a disorganized collection of facts, numbers, audio clips, images, etc …… a DATABASE is: a shared collection of logically related data stored in an organised structure which provides the ability to interact with data and to extract salient information and knowledge. The Evolution of Databases – Manual Filing Systems An organised collection of documents in separate files – logically related Held in locked filing cabinets, possibly over different premises Data can be manipulated – accessed, deleted, updated, etc And can be searched - first entry / indexing system Can work well …. if there are a small number of entries or…. a large number of entries, but used to store and retrieve only. BUT …. can be difficulties for anything more complex Data is distributed (decentralised) and may contain duplicates May be difficult to extract information and knowledge. e.g., Total Annual Salary Bill across all Departments Introduction to Databases & the Relational Model The Evolution of Databases – File-Based Approaches First attempt at computerisation – modelled on the manual filing system Initially, an application-centred approach, where an application program worked on associated data files – e.g., a payroll system. Later, files could be shared among applications to reduce data duplication. PROBLEMS: No data centralisation (leading to data isolation and separation) Poor file design can lead to data duplication (or data redundancy). Updating multiple record copies is inefficient and may lead to loss of data integrity Data dependence – some data in the system may be dependent on others and should be updated together Application programs need a knowledge of file storage structures (queries are not independent of the data files or storage structures) Incompatible file formats Security (file level vs. data item level) Data organisation and structure is limited …. so limited queries are possible Introduction to Databases & the Relational Model Explanation of Terms – Data Integrity Accurate data is vital in all information systems Data integrity is concerned with: Consistency (is duplicate data identical?) Correctness (is the stored data accurate?) Using Maintaining data integrity involves: Integrity ensuring data is designed correctly (data model) Constraints ensuring data is entered correctly (validation) ensuring data is free from corruption, modification or unauthorised disclosure (programming errors or hacks) ensuring data correctness is maintained (backup, recovery, transaction integrity maintenance) Explanation of Terms – Data Dependency A dependency occurs when information stored in a database table (or in a file) uniquely determines other information stored in the same table (or file). i.e., knowing the value of one piece of data is enough to tell you the value of another in the same table (or file).