The document discusses querying a database using SQL commands, including writing queries, creating and editing tables, and exporting/importing data to and from Excel. It covers creating indexes to improve query performance and manipulating data in database tables.
The document discusses querying a database using SQL commands, including writing queries, creating and editing tables, and exporting/importing data to and from Excel. It covers creating indexes to improve query performance and manipulating data in database tables.
The document discusses querying a database using SQL commands, including writing queries, creating and editing tables, and exporting/importing data to and from Excel. It covers creating indexes to improve query performance and manipulating data in database tables.
The document discusses querying a database using SQL commands, including writing queries, creating and editing tables, and exporting/importing data to and from Excel. It covers creating indexes to improve query performance and manipulating data in database tables.
commands. Writing simple SQL queries. Creating and editing tables. Creating indexes to improve performance. Exporting and importing data from/to database tables to/from Excel.
19-09-2019 19cse101 Computer Essentials Unit-II 1
Recap Data is nothing but facts and figures which are generally raw and unprocessed.
• For example: When you visit any website, they might store you IP address, that is data, in return they might add a cookie in your browser, marking you that you visited the website, that is data, your name, it's data, your age, it's data.
Data becomes information when it is processed, turning it into something meaningful.
• Like, based on the cookie data saved on user's browser, if a website can analyze that generally men of age 20-25 visit us more, that is information, derived from the data collected.
19-09-2019 19cse101 Computer Essentials Unit-II 2
Database Management System (DBMS) • DBMS is a software application/tool used by end users to access the data stored in the database files. • A DBMS is also used to perform administrative tasks on the database . • DBMS is a collection of applications that allow users and other programs to capture and analyze data by providing additional functionality like reporting services to help you create, deploy and manage reports for your organization. 19-09-2019 19cse101 Computer Essentials Unit-II 3 Characteristics of Database Management System • A database management system has following characteristics: • Data stored into Tables: Data is never directly stored into the database. Data is stored into tables, created inside the database. DBMS also allows to have relationships between tables which makes the data more meaningful and connected. You can easily understand what type of data is stored where by looking at all the tables created in a database. • Reduced Redundancy: In the modern world hard drives are very cheap, but earlier when hard drives were too expensive, unnecessary repetition of data in database was a big problem. But DBMS follows Normalisation which divides the data in such a way that repetition is minimum. • Data Consistency: On Live data, i.e. data that is being continuously updated and added, maintaining the consistency of data can become a challenge. But DBMS handles it all by itself. • Support Multiple user and Concurrent Access: DBMS allows multiple users to work on it(update, insert, delete data) at the same time and still manages to maintain the data consistency. • Query Language: DBMS provides users with a simple Query language, using which data can be easily fetched, inserted, deleted and updated in a database. • Security: The DBMS also takes care of the security of data, protecting the data from un-authorised access. In a typical DBMS, we can create user accounts with different access permissions, using which we can easily secure our data by restricting user access.
19-09-2019 19cse101 Computer Essentials Unit-II 4
Advantages of DBMS
• Minimal data redundancy.
• Easy retrieval of data using the Query Language. • Reduced development time and maintenance need. • With Cloud Datacenters, we now have Database Management Systems capable of storing almost infinite data. • Seamless integration into the application programming languages which makes it very easier to add a database to almost any application or website. 19-09-2019 19cse101 Computer Essentials Unit-II 5 Disadvantages of DBMS
• It's Complexity • Except MySQL, which is open source, licensed DBMSs are generally costly. • They are large in size.
19-09-2019 19cse101 Computer Essentials Unit-II 6
Users of DBMS • Administrators − Administrators maintain the DBMS and are responsible for administrating the database. They are responsible to look after its usage and by whom it should be used. They create access profiles for users and apply limitations to maintain isolation and force security. • Designers − Designers are the group of people who actually work on the designing part of the database. They keep a close watch on what data should be kept and in what format. • End Users − End users are those who actually reap the benefits of having a DBMS. End users can range from simple viewers who pay attention to the logs or market rates to sophisticated users such as business analysts. 19-09-2019 19cse101 Computer Essentials Unit-II 7 DBMS Architecture: 1-Tier, 2-Tier & 3-Tier
• DBMS architecture helps in design, development,
implementation, and maintenance of a database. A database stores critical information for a business. Selecting the correct Database Architecture helps in quick and secure access to this data.
19-09-2019 19cse101 Computer Essentials Unit-II 8
1 tier Architecture
• The simplest of Database Architecture are 1 tier where
the Client, Server, and Database all reside on the same machine. • Anytime you install a DB in your system and access it to practise SQL queries it is 1 tier architecture. • But such architecture is rarely used in production.
19-09-2019 19cse101 Computer Essentials Unit-II 9
2-tier Architecture
• A two-tier architecture is a database architecture where
• Presentation layer runs on a client (PC, Mobile, Tablet, etc) • Data is stored on a Server. • An application interface which is called ODBC (Open Database Connectivity) an API which allows the client-side program to call the DBMS. Today most of the DBMS offers ODBC drivers for their DBMS. 2 tier architecture provides added security to the DBMS as it is not exposed to the end user directly. 19-09-2019 19cse101 Computer Essentials Unit-II 10 Example of Two-tier Architecture is a Contact Management System created using MS- Access.
3-tier Architecture • 3-tier schema is an extension of the 2-tier architecture. 3-tier architecture has following layers • Presentation layer (your PC, Tablet, Mobile, etc.) • Application layer (server) • Database Server
• This DBMS architecture contains an Application layer between the user and the DBMS, which is responsible for communicating the user's request to the DBMS system and send the response from the DBMS to the user. • The application layer(business logic layer) also processes functional logic, constraint, and rules before passing data to the user or down to the DBMS 19-09-2019 19cse101 Computer Essentials Unit-II 13 DBMS components
◼ Hardware – the physical computer system that allows physical access to data. ◼ Software – the actual program that allows users to access, maintain, and update physical data. ◼ Data – stored physically on the storage devices ◼ Users – End users - Normal user and DBA (Database Administrator) Application programs ◼ Procedures – a set of rules that should be clearly defined and followed by the users. 19-09-2019 19cse101 Computer Essentials Unit-II 14 Database Schema
• A database schema is the skeleton structure that
represents the logical view of the entire database. • It defines how the data is organized and how the relations among them are associated. • A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. 19-09-2019 19cse101 Computer Essentials Unit-II 15 19-09-2019 19cse101 Computer Essentials Unit-II 16 • A database schema can be divided broadly into two categories − • Physical Database Schema − This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage. • Logical Database Schema − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.
What is a Database (db)? • A Database (db) is an organized collection of data, typically stored in electronic format.
• It allows you to input, manage, organize and retrieve data quickly.
• Traditional databases are organized by records (rows), fields (columns) stored in tables which are stored in database files. • It’s a file used to store information.
• A database table is a collection of rows and columns that is used to organize information about a single topic or object. Each row within a table corresponds to a single record and contains several different attributes that describe the row.
• A database table is the most common and simplest form of data storage in a relational database.
What is a Database (db)? • A relational database is a collection of tables of data all of which are formally described and organized according to the relational model. Each table must identify a column or group of columns, called the Primary Key, to uniquely identify each row.
• Entity • Object comprised of various pieces of data. • Attribute • Piece of information (data) that describes an entity.
• Relational Database Management Software (RDBMS) is a software system designed to allow the definition, creation, querying and updating of data stored in a relational database. • A few examples of RDBMS include: • Microsoft Access • MYSQL • Oracle • MySql • IBM DB2 • PostgreSQL • Amazon SimpleDB (cloud based) etc
What is a Database (db)? • The main differences between DBMS and RDBMS are: • A DBMS has to provide some uniform methods independent of a specific application accessing the information that is stored in the tables. • RDBMS adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. • DBMS does not impose any constraints or security with regard to data manipulation, while RDBMS does utilize an internal security model. • RDBMS is the basis for Structured Query Language (SQL).
Database models • A database model • defines the logical design of data. • Describes the relationships between different parts of data. • 3 models • Hierarchical model • Network model • Relational model
System) • external view • The data are represented as a set of relations. • A relation is a two-dimensional table. • This doesn’t mean that data are stored as tables; the physical storage of the data is independent of the way the data are logically organized.
Figure 14-6 Relation ◼ Name – each relation in a relational database should have a name that is unique among other relations. ◼ Attribute – each column in a relation. The degree of the relation – the total number of attributes for a relation. ◼ Tuple – each row in a relation. The cardinality of the relation – the total number of rows in a relation.
Operations on relations • In a relational database, we can define several operations to create new relations out of the existing ones. • Basic operations: • Insert • Delete • Update • Select • Project • Join • Union • Intersection • Difference
◼ An unary operation. ◼ It is applied to one single relation and creates another relation. ◼ The tuples in the resulting relation are a subset of the tuples in the original relation. ◼ Use some criteria to select
Project operation ◼ An unary operation. ◼ It is applied to one single relation and creates another relation. ◼ The attributes in the resulting relation are a subset of the attributes in the original relation.
Union operation ◼ A binary operation. ◼ Creates a new relation in which each tuple is either in the first relation, in the second, or in both. ◼ The two relations must have the same attributes.
Intersection operation ◼ A binary operation. ◼ Creates a new relation in which each tuple is a member in both relations. ◼ The two relations must have the same attributes.
Difference operation ◼ A binary operation. ◼ Creates a new relation in which each tuple is in the first relation but not the second. ◼ The two relations must have the same attributes.
