01 Intro to DBMS

Intro to DBMS

Intro to DBMS

Textbook: https://opentextbc.ca/dbdesign01/

Database

• = organised collection of structured information, or data, typically stored electronically in a computer system
• = collection of data stored in an organised or logical manner
• Usually controlled by a database management system (DBMS)
• Allows us to access and manage the data

Types of databases (in syllabus)

• Flat-file databases: stores data in a single table
• Generally in plain-text files, where each line holds one record
• Fields in the record are separated using delimiters (e.g. tabs, commas)
• Hierarchical databases:
• Data stored in a parent-children relationship nodes
• Records contain actual data + info about their groups of parent/child relationships
• Network databases:
• Uses a network structure to create relationship between entities
• Are hierarchical databases but can have relationship with multiple entities (unlike hierarchical databases where 1 node can have 1 parent only)
• Relational databases: collection of data organised into a table structure
• = a collection of relational tables, whee in each table:
• Values are atomic
• Columns are of the same kind
• Rows are unique
• Order of columns is insignificant
• Each column has a unique name
• Items organised as a set of tables with columns and rows
• Provides the most efficient and flexible way to access structured info
• Object-oriented databases: database that subscribes to a model with information represented by objects (“plug everything into objects”)
• Information is represented in the form of objects, as in object-oriented programming
• NoSQL / nonrelational databases:
• != no structure
• Allows structured and semistructured data to be stored and manipulated

DBMS

• Comprehensive database software program
• Serves as an interface between the database and its end users or programs, allowing users to retrieve, update, and manage how info is organised and optimised
• Facilitates additional administrative operations (e.g. performance monitoring, tuning, and backup and recovery)
• E.g. MySQL, Microsoft Access, Microsoft SQL Server, Oracle Database, PostgreSQL, SQLite
• Other advantages:
• Controlling redundancy in data storage, development, & maintenance efforts
• Restricting unauthorized access to data
• Providing persistent storage for program objects and data structures
• Efficient query processing
• Providing backup and recovery systems
• Providing multiple interfaces to different classes of users
• Representing complex relationships among data
• Enforcing integrity constraints on the database
• Permitting inference and actions using rules. Typical actions are performed by triggers and stored procedures
• Potential for enforcing standards
• Reduced application development time
• Flexibility to change data structures
• Availability of up-to-date information
• Economies of scale and sharing of data among multiple users
• When not to use: (main inhibitors / costs of using DBMS)
• High initial investment and possible need for additional hardware and/or personnel
• Overhead for providing generality, security, recovery, integrity, and concurrency control
• DBMS may be unnecessary if:
• Database and applications are simple, well defined, and not expected to change
• There are stringent real-time requirements that may not be met because of DBMS overhead
• Access to data by multiple users is not required
• DBMS may be adequate/enough if:
• Database system is not able to handle complexity of data because of modeling limitations
• Database users need special operations not supported by DBMS

Database systems

• = An implementation according to the rules of a theoretical model
• Not just a collection of data
• 3-level model
• External Level (User Views): describes a part of the database that is relevant to a particular user
• Excludes irrelevant data + data which user is not authorised to access
• Conceptual Level: describes what data is stored within the whole database and how the data is inter-related
• Does not specify how data is physically stored
• Controlled by database administrator (DBA) who has access to DBMS
• Internal level: how database is physically represented on the computer system
• Describes how data is actually stored in the database and on the computer hardware
• Controlled by DBMS software
• Objectives of 3-level architecture: to separate user’s view
• Allows independent customised user views
• Each user should be able to access the same data, but have a different customised view of the data
• These views should be independent: changes to one view should not affect others
• Hides the physical storage details from users
• Users should not have to deal with physical database storage details
• Database administrator should be able to change database storage structure without affecting user’s views
• Internal structure of database should be unaffected by changes to the physical aspects of the storage
• E.g. structure of database not affected when shifting database to another new disk
• 

Database vs file-based

• Self describing nature of a database system
• Database system contains database itself AND a complete definition / description of database structure and constraints
• Definition stored in DBMS catalog, which contains info like structure of each file, the type and storage format of each data item, and various constraints on data
• Meta-data: info stored in catalog ⇒ describes structure of database
• In traditional file processing, data definition is typically part of the application programs, which are constrained to work with only 1 specific database, whose structure is declared in the application programs
• 
• Insulation between programs and data, and data abstraction
• Program-data independence: allows changing of data storage structures and operations without having to change the DBMS access programs
• Structure of data files is stored in the DBMS catalog separately from the access programs
• In traditional file processing, structure of data files is embedded in application programs, so any changes to structure of a file may require changing all programs that access that file
• Supports multiple views of the data
• Database has many types of users, each of whom may require a diff perspective/view of the database; a view may be a subset of the database
• Each user may see a diff view of the database, which describes only the data of interest to that user
• 
• Sharing of data and multiuser transaction processing
• Ensure that current transactions operate correctly and efficiently
• Updating of same data is done in a controlled manner so that results of the updates is correct
• A multiuser DBMS must allow multiple users to access the database at the same time and ensure that users trying to update the same data do so in a controlled manner so results is correct
• Transaction = a logical unit that is independently executed for data retrieval or updates
• Must be: (ACID)

Example: transfer of funds from one account to another
Atomicity	All changes to data are performed as if they are a single operation ⇒ all the changes, or none, are performed
	Ensures that if a debit is made successfully from one account, the corresponding credit is made to the other account
Consistency	Data is in a consistent state when a transaction starts and when it ends
	Ensures total value of funds in both accounts is the same at start and end of each transactions
Isolation	Intermediate state of a transaction is invisible to other transactions ⇒ transactions that are run concurrently appear to be serialised
	Ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither
Durability	After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure
	Ensures that changes made to each account will not be reversed (i.e. remain permanent even after a system crash)

Q: Design a way to keep information of the following using a text file. Student’s information consists of Name, NRIC, Date of birth, Contact no, Class, Address, Subjects, Subject teachers and Form teacher. How should the data be kept using a text file? Do you see any issues if data is kept this way?

Ans: Text file – formatted with a delimiter*, Multiple lines format *Delimiter = a character or sequence of characters that marks the boundary between separate pieces of data Use an application to read and write the content Excel, Google sheet Encrypt the file to secure it Issues: Disorganized, difficult to change the information, difficult to read Difficult to reorganise the data to meet specific needs Separating of data for different users of the data is difficult Programs written in correspondence to the file are subject to changes in the data structure of the file Program needs to be shared … must find a way to distribute the program when there is an update to the program … User need to have some understanding of the data How should the data be kept using a text file? Delimited with a symbol : | or , or ; Fixed space columns – Name – 100 characters, NRIC – 10 characters Issues: Data redundancy: the same data is being stored more than once E.g. subject teacher name is stored in multiple records Data Integrity: duplication of data and inconsistent data1 Student name could be entered twice if there is a lack of validation Subject Teacher’s name is key in multiple times for each student in the class. If there is a change of the subject teacher for a particular class, all records need to be changed Inconsistency might happen when only one record of the same subject teacher’s name is updated

Q: The student information needs to be shared with the following groups : The Principal Office The teacher who is planning the timetable The finance department Propose a way you can allow all of them to have the information. What are the limitations (if any)?

Ans: Using email to share Share using Google sheets / doc / drive Peer to peer network Client – server - centralised storage system Shared cloud drive - authorised personnel can make edits or view updates of the file in real-time. Difficult to update and at the same time all parties can see the updated information. If shared by email, need to resend email There will have multiple copies of the same files and if there are some changes to one of them, all files will need to be updated, otherwise there will be different versions of the file Client and server network to share the file … limitation : high initial set up cost , maintenance cost, security needs to be address and the requirement for a professional to maintain… data is unsynced most of the time All of them would view the same set of data, which may not be suitable all the time. That would be when some data is inapplicable or not useful to users or when some data are confidential in nature and can be accessed only by selected users Different recipients need different information it is troublesome for them to sieve out the information they need. It is also hard to read data in a text file due to the formatting. Lack of Data Privacy The information is shown to anyone who has access to the file even though they don’t need to know those information. This is due to the fact that there are no control of access to part of a file. A way to resolve this problem is to have different files that contains different data for different person who needs access. With this approach, we have data duplication across the files. We will have data redundancy

The students’ information need to be updated by the form teacher when he/she finds out that some information is not correct. The teacher, who is planning the timetable, needs to update the subjects if he/she finds a mistake. The Principal Office and Finance department would need the updated information anytime. Propose a way you can fulfill the above requirements to make everyone happy using your proposed designed text file. What are the limitations (if any)?

Data Integrity – duplication of data and inconsistent data Student name could be entered twice if there is a lack of validation Subject Teacher’s name is key in multiple time for each student in the class. If there is a change of the subject teacher for a particular class, all records needs to be change. Data redundancy – the same data is being stored more than once Example subject teacher name is stored in multiple records Data Dependency Concerns – when a program is used to access the files The file structures have been defined to suit specific program and they will not be suited to support new applications. Considerable time will be required to create a new application to make use of the data. Any changes to the file structure or if there is a need for a change in the data stored, which will require the existing file to be re-written, this in turn will require the programs to be re-written so that the new file can be read correctly.

ANDREA TAN KAI XUAN AND LOW XIWEN, JADE HCI - Lesson 1 activities

Comments from the Word document

Footnotes

1. Comment by ANDREA TAN KAI XUAN HCI: Whats the difference ↩