Download Introduction to Database Systems and SQL and more Study notes Biology in PDF only on Docsity!
LESSON 1: DATABASE SYSTEM
➢ WHY DATABASES?
- Data is ubiquitous (abundant, global, everywhere) and pervasive (unescapable, prevalent, persistent)
- It makes the data persistent and shareable in a secure way.
- Data is not only ubiquitous (everywhere) and pervasive (widespread), but also essential for organization to survive and prosper (succeed).
➢ DATA VS INFORMATION a. DATA
- Consists of raw facts. “Raw” indicates that the facts are not yet processed to reveal their meaning.
- Data constitutes the building blocks of information.
b. INFORMATION
- The result of processing raw data to reveal its meaning.
- Is produced by processing data.
- Is used to reveal the meaning of data.
- Accurate, relevant, and timely information is the key to good decision making.
- Good decision making is the key to organizational survival in a global environment.
➢ INTRODUCING THE DATABASE
- “DATABASE” is a shared, integrated computer structure that stores a collection of data. a. End user- are raw facts of interest to the end user. b. Metadata/ Data about Data (which the end user data is integrated(united) and managed.)
- it's the data characteristics and the set of relationships that links the data found within the database.
➢ A DATABASE MANAGEMENT SYSTEM
(DBMS) is a collection of programs that manage the database structure and control access to the data stored in the database.
- Database resembles a very well-organized electronic filing cabinet in which powerful software (DMBS) helps manage the cabinet’s contents.
❖ ROLES & ADVANTAGES OF DBMS
a. Improved “data sharing” b. Improved “data security” c. Better “data inconsistency” d. Improved “data access” e. Improved “decision making” f. Increased “end-user productivity”
➢ TYPES OF DATABASE (users) a. Single-user database
- Software application designed used by a single individual for managing and storing data. ● Desktop database
b. Workgroup database ● Workgroup database- less than 50 users. ● Enterprise database- more than 50 users
➢ TYPES OF DATABASE (location) a. Centralized database- located in a single site. b. Distributed database- distributed across several sites.
➢ TYPES OF DATABASE (technology) a. Cloud database- a database that is created and maintained using cloud data services, such as Microsoft azure or amazon AWS.
➢ TYPES OF DATABASE (use) a. Operational database- primarily used for day-to-day transactional activities. b. Analytical database- designed for complex querying and data analysis to support business intelligence and decision-making process.
➢ TYPES OF DATABASE
● Social Media- refers to web and mobile technologies that enable “anywhere, anytime, always on” human interactions. Websites such as google, facebook, twitter, and LinkedIn capture vast amounts of data about end users and consumers. This data grows exponentially and requires the use of specialized database systems.
➢ WHY DATABASE DESIGN IS IMPORTANT? ● Database design is all about how you
set up the structure to store user data, and it's super important. Even if you have a great Database Management System (DBMS), it won't work well
● ➢ EVOLUTION OF FILE SYSTEM DATA PROCESSING a. Manual File System- uses paper and pencils for record-keeping. b. Computerized file systems- relies on computers for managing records and data.
➢ BASIC FILE TERMINOLOGIES a. Data- raw facts b. Field- character or group of characters that has a special meaning. c. Record- logically connected sets of one or more files that describe a person, place, or thing. d. File- collection of related records.
➢ PROBLEMS WITH FILE DATA
PROCESSING
(Problem with file systems) a. Lengthy development times b. Di culty of getting quick answers c. Complex system administration d. Lack of security and limited data sharing e. Extensive programming
➢ STRUCTURAL DATA DEPENDENCE
a. Structural dependence – access to a file is dependent on its structure. b. Structural independence – changing the file structure without a ecting the applications ability to access the data.
➢ DATA REDUNDANCY a. It exists when the same data is stored unnecessarily at di erent places. b. Poor data security c. Data inconsistency d. Data entry errors e. Data integrity problems
➢ DATA ANOMALIES
- It develops when not all of the required changes in the redundant are made successfully.
➢ DATABASE SYSTEMS
- The term database system refers to an organization of components that define and regulate the collection, storage, management and use of data within a database environment.
➢ DBMS FUNCTION
- Data dictionary management
- Data storage management
- Data transformation and presentation
- Security management
- Multi User access control
- Backup and recovery management
- Data integrity management
- Database access languages and application programming interface
- Database communication interfaces
➢ MANAGING THE DATA SYSTEM
- Increased costs management complexity
- Maintaining currency
- Vendor dependence
- Frequent upgrade/replacement cycles
\\
LAB 2: INTRODUCTION TO SQL
- Database language allows you to create/edit database and table structures.
➢ SQL DICTIONARY
· CREATE TABLE - Creates a new table in the
user’s database schema
· NOT NULL - Ensures that a column will not
have null value
· UNIQUE - Ensures that a column will not
have duplicate values
· PRIMARY KEY - Defines a primary key for a
table. A combination of a NOT NULL and UNIQUE. Ensures that a column have a unique identity
· FOREIGN KEY - Defines a foreign key for a
table
· DEFAULT - Defines a default value for a
column (when no value is given)
· ALTER TABLE - Modifies a table’s definition
(adds, modifies, or deletes attributes or constraints)
· CREATE TABLE AS - Creates a new table
based on a query in the user’s database schema
· DROP TABLE - Permanently deletes a table
(and its data)
· DROP INDEX - Permanently deletes an index
· DROP VIEW - Permanently deletes a view
· INSERT - Inserts row(s) into a table
· SELECT - Selects attributes from rows in
one or more tables or views
· WHERE - Restricts the selection of rows
based on a conditional expression
· GROUP BY - Groups the selected rows
based on one or more attribute
· HAVING - Restricts the selection of grouped
rows based on a condition
· ORDER BY - Orders the selected rows based
on one or more attributes
· UPDATE - Modifies an attribute’s values in
one or more table’s rows
· DELETE - Deletes one or more rows from a
table
· =, <, >, <=, >=, <>, != - Used in conditional
expressions
· AND/OR/NOT - Used in conditional
expressions
· BETWEEN - Checks whether an attribute
value is within a range
· IS NULL - Checks whether an attribute value
is null
· LIKE - Checks whether an attribute value
matches a given string pattern
· IN - Checks whether an attribute value
matches any value within a value list
· EXISTS - Checks whether a subquery returns
any rows
· DISTINCT - Limits values to unique values
· COUNT - Returns the number of rows with
non-null values for a given column
· MIN - Returns the minimum attribute value
found in a given column
· MAX - Returns the maximum attribute value
found in a given column
· SUM - Returns the sum of all values for a
given column
· AVG - Returns the average of all values for a
given column
· XAMPP - XAMPP is the most popular PHP
development environment, completely free, easy to install Apache distribution containing MariaDB, PHP, and Perl.
· Data Type - A data type defines what kind of
value a column can hold: integer data, character data, monetary data, date and time data, binary strings, and so on. e.g. varchar, int, decimal, char…
· Constraints – used to specify rules for the
data in a table. e.g. Primary Key, Unique, Not Null, Foreign Key, Check, Default
