Introduction to Database Design
dates parameters
DD-MONTH-YEAR DD-MON-RR
Select Statement
The SELECT statement, or command, allows the user to extract data from tables, based on specific criteria. Syntax: SELECT column_name, column_name FROM table_name; SELECT * FROM table_name;
Security Problems & Concurrency Access
•Security Problems: Who should be allowed to access the data? •Can the employee responsible for managing your credit card account have access to your checking account balance? •Concurrency Access: How can multiple users access and maybe modify the data at the same time? •In a file-system, when a file is open by a user, other users can open it in read-only mode.
What is this benefit of a Database: Transaction processing
•When a user makes several changes to the data, all the changes must be saved or none of the changes are saved. This ensures that the data remains consistent and no other user sees data in the process of being updated (e.g. transferring money from one account to another).
data redundancy
Exists when the same data is stored unnecessarily in different places. •If you have more than 1 user responsible for maintaining the data on their computer, you will have multiple copies of that data. •When a change needs to be made to a piece of data, it now needs to be made in several places. •This can lead to data inconsistency: when various copies of the same data are conflicting. •Wastes storage space and duplicates the effort.
read and write privileges
the ability to both read and modify a file
AS keyword
we can change the column name when we use AS
What is this benefit of a Database: self-describing
a database is referred to as a self-describing of a database system b/c it not only contains the database itself, but also metadata, which is the description of the data and relationship between tables in the database
IN loop
what if i wanted to find out different information between 2 different tables We want to find something: select salary from employee where salary between 50000 and 70000; now we want to find info about those numbers such as names: select first_name, last_name from employee where salary in (select salary from employee where salary between 50000 and 70000);
What are the 7 characteristics and benefits of Database?
1 - self-describing 2 - insulation between program and data 3 - support for multiple views of data 4 - sharing of data and multiuser system, Enforcement of integrity constraints 5 - Restrictions of unauthorized access 6 - transaction processing 7 - backup and recovery facilities
What is this benefit of a Database: 1- Sharing of Data and multiuser system 2 - Enforcement of integrity constraints
1 - •Sharing of data and multiuser system •Data in the database can be shared by multiple users •Multiple users are able to access the data simultaneously because of a feature called concurrency control. This feature ensures that the data integrity is maintained. 2 - •Enforcement of integrity constraints •DBMS provide the ability to enforce certain constraints on the data to ensure it remains valid. • A database constraint is a rule or a restriction applied to a field. •Data type is a restriction that determines the type of data to store in a field (a number, a character, etc.) •Data uniqueness is a constraint that indicates which field must be unique (called primary key) (e.g. banner id must be unique)
File-based system
File-based System •One way to keep information is in a file on the computer or in a filing cabinet in an office. •In a company, different departments manage different pieces of information and save it in a different file Example of Banking System: •Debit and Credit Cards• Bank Accounts•Loans and Mortgage • Employees
data type
determines the sort of data permitted in a field, for example numbers only
concurrency control strategies
features of a database that allow several users access to the same data item at the same time
SELECT statement with WHERE criteria
sometimes you might want to focus on a portion of the publishers table, such as only publishers that are in Vancouver SELECT column FROM table, WHERE column? BETWEEN 20 and 50 (includes 20 and 50)
What is this benefit of a Database: Support of multiple views
•A DBMS allows multiple users to have access to the database simultaneously •A view is a subset of the database, which is defined and dedicated for particular users of the system. •Multiple users in the system might have different views of the system depending on their privilege access and their use.
What is this benefit of a Database: Backup and recovery facilities
•Backup and recovery are methods that allow you to protect the data stored from being lost. •The DBMS has a built in capability of backing up the data in case of a hard drive failure. •In case of a problem, the data can be restored.
What is Data?
•Data are factual information such as statistics about objects (facts). •Data can be a person, place, event, flight, an item being sold, etc. •A single fact is an element of data or a data element.
Useful Operations using DBMS
•Sorting the data (alphabetically, highest to lowest) •Matching (finding students that will graduate this year) •Calculating values (total, average) •Matching (finding students that will graduate this year) •Calculating values (total, average) •Arranging the data (viewing last name first or banner id) •Skipping fields (displaying the data without the address and phone #) •Aggregation (creating summaries and reports) •Link (an object that allows you to access data from another table/database)
Database Approach
•The difficulties that arise from using a file-based system have prompted the development of a new approach to managing large amounts for information: Database Approach • Database Management Software: A powerful software tool that allows the storage, manipulation and rapid search and retrieval of data in different ways. •Companies keep track of customer information in a database •Colleges keep track of student information in a database •Stores keep track of inventory items in a database
we often need to access and re-sort data for. various uses. These may include?
- creating mailing lists writing management reports - generating lists of selected news stories - identify various client needs
What are the disadvantage of file-based system
Data Redundancy •Data Inconsistency •Data Isolation •Data Integrity •Security Problems •Data Integrity •Security Problems •Concurrency Problems
What is RDBMS?
RDBMS stands for Relational Database Management System.
repository
a place designated for storage a database of data
Data Element/Element
a single fact is an element of a data, or data element
view
a subset of the database
Metadata
data that describes other data and relationship between tables in the database
What is DBMS?
database management system •A collection of programs that allows users to create and maintain databases and control access to them •Its main goal is to provide an environment that is both convenient and efficient for users to retrieve and store information
IN statement
specify multiple possible values SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value 2,..) select * from product where product_id in (3, 4, 5); same as saying: SELECT* from PRODUCT WHERE product_id = 3 or product_id = 4 or product_id = 5;
BETWEEN statement
takes 2 values and will give you anything in between these 2 values can use strings and numbers SELECT * FROM Customers WHERE price > 50 and price < 100; SELECT * FROM Customers WHERE price BETWEEN 50 and 100;
read-only access
the ability to both read file but not make changes
Data Isolation and Data Integrity
transaction should be made from one acct to another acct simultaneously for accts to be accurate. •Data Isolation: When should changes made by 1 user be visible to another user? •This is a problem in systems with concurrent users (users using the system at the same time). • Data Integrity: Assuring that the data in the database is correct and consistent. •Who ensures that the interest rate stored is in % format? 3% or 0.03? •What if the banner id of a user must start with an @, but someone forgets to add it? Who validates that it is accurate? Who validates that the banner id is unique?
What is a Database?
• A database is an organized, shared collection of related data used to support the activities of a particular organization. •It can be viewed as a repository of data that is defined once and then accessed by various users. •Databases come in many sizes and complexities.
What is this benefit of a Database: Insulation between program and data/Program Data Independence
•Insulation between program and data •In a file-based system, if the structure of the file storing the data changes, then ALL the programs that access the file might need to be changed. •In a database approach, the data structure is stored in the database system catalogue. There is insulation between the programs and the data. This is also called program-data independence.
What are the properties of a database?
•It is a representation of some aspect of the real world. It's a collection of data elements (facts) representing the real-world information. •Example from MCC: Students, Faculty, Course Listings, Rooms, etc •It is logical, coherent and internally consistent •It is designed, built and populated with data for a specific purpose •Each data item is stored in a field •A combination of fields makes up a table• A database can contain many tables • A table of students • A table of courses • A table of program majors
What are some benefits of using a DB/DBMS?
•Managing information means taking care of the information so that it works for us and is useful for the tasks we perform. •DBMS saves data from being accidentally disorganized •Data becomes accessible and integrated with the rest of the system
What is this benefit of a Database: •Restrictions of unauthorized access
•Not all users of a DBMS should have the same level of access •Some data should be hidden from some users•Some data should only be modified by some users (read-only access)
Uses of a Database
•Registered users on a website •Tracking clients/customers for any company/organization •Medical records •Address book in your email •Airline reservations
