Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

unit-3: rdbms (relational database management systems), Lecture notes of Design

Design

is a software package that can be used for creating and managing databases. 3. A Relational Database Management System (RDBMS) is a database management system ...

Typology: Lecture notes

2022/2023

Uploaded on 03/01/2023

amodini
amodini 🇺🇸

4.7

(19)

257 documents

1 / 9

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
UNIT-3: RDBMS (RELATIONAL DATABASE MANAGEMENT SYSTEMS)
1
A database is an organized collection of data.
2
A database management system is a software package with computer programs that
controls the creation, maintenance, and use of a database. It allows organizations to
conveniently develop databases for various applications. A database is an
integrated collection of data records, files, and other objects. A DBMS allows
different user application programs to concurrently access the same database.
3
Well known DBMSs include Oracle, IBM DB2, Microsoft SQL Server, Microsoft
Access, PostgreSQL, MySQL, FoxPro, and SQLite.
4
Data can be organized into two types:
• Flat File: Data is stored in a single table. Usually suitable for less amount of data.
• Relational: Data is stored in multiple tables and the tables are linked using a
common field. Relational is suitable for medium to large amount of data.
5
Database servers are dedicated computers that hold the actual databases and run
only the DBMS and related software.
6
Advantages of Database
Reduces Data Redundancy no chance of encountering duplicate data
Sharing of Data the users of the database can share the data among themselves.
Data Integrity Data integrity means that the data is accurate and consistent in
the database.
Data Security Only authorised users are allowed to access the database and
their identity is authenticated using a username and password.
Privacy The privacy rule in a database states that only the authorized users can
access a database according to its privacy constraints.
Backup and Recovery Database Management System automatically takes care
of backup and recovery.
Data Consistency Data Consistency means there should be multiple
mismatching copies of the same data.
7
Features of Database
A database can have one or many tables.
Each table in a database contains information about one type of item.
record uniqueness Uniqueness helps to avoid accidental duplication of
records caused by user or computer error. This unique field is called the
Primary Key (PK).
A primary key is a unique value that identifies a row in a table.
Every database table should have one or more fields designated as key.
8
When primary key constraint is applied on one or more columns then it is known
as Composite Primary Key.
pf3
pf4
pf5
pf8
pf9

Partial preview of the text

Download unit-3: rdbms (relational database management systems) and more Lecture notes Design in PDF only on Docsity!

UNIT-3: RDBMS (RELATIONAL DATABASE MANAGEMENT SYSTEMS)

(^1) A database is an organized collection of data. (^2) A database management system is a software package with computer programs that controls the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications. A database is an integrated collection of data records, files, and other objects. A DBMS allows different user application programs to concurrently access the same database. (^3) Well known DBMSs include Oracle, IBM DB2, Microsoft SQL Server, Microsoft Access, PostgreSQL, MySQL, FoxPro, and SQLite. (^4) Data can be organized into two types:

  • Flat File: Data is stored in a single table. Usually suitable for less amount of data.
  • Relational: Data is stored in multiple tables and the tables are linked using a common field. Relational is suitable for medium to large amount of data. (^5) Database servers are dedicated computers that hold the actual databases and run only the DBMS and related software. (^6) Advantages of Database Reduces Data Redundancy  no chance of encountering duplicate data Sharing of Data  the users of the database can share the data among themselves. Data Integrity  Data integrity means that the data is accurate and consistent in the database. Data Security  Only authorised users are allowed to access the database and their identity is authenticated using a username and password. Privacy  The privacy rule in a database states that only the authorized users can access a database according to its privacy constraints. Backup and Recovery  Database Management System automatically takes care of backup and recovery. Data Consistency  Data Consistency means there should be multiple mismatching copies of the same data.

7 Features of Database

 A database can have one or many tables.  Each table in a database contains information about one type of item.  record uniqueness  Uniqueness helps to avoid accidental duplication of records caused by user or computer error. This unique field is called the Primary Key (PK). A primary key is a unique value that identifies a row in a table.  Every database table should have one or more fields designated as key. (^8) When primary key constraint is applied on one or more columns then it is known as Composite Primary Key.

(^9) The foreign key identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. (^10) A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables. (^11) Fill in the blanks:

  1. A Database is an organized collection of data.
  2. A Database Management System (DBMS) is a software package that can be used for creating and managing databases.
  3. A Relational Database Management System (RDBMS) is a database management system that is based on the relational model. 4.Three popular DBMS software are Open Office Base, MySQL, & Microsoft SQL Server.
  4. A Primary Key is a unique value that identifies a row in a table.
  5. Composite Key is a combination of One or More columns. Short Answer Questions
  6. What does DBMS stands for? Ans Database Management System
  7. What does RDBMS stands for? Ans Relational Database Management System
  8. How is data organized in a RDBMS? Ans Data in RDBMS is organized in the form of tables 4.State the relationship and difference between a primary and foreign key? Ans Primary Key Foreign Key There can only be 1 primary key in a table There can be multiple Foreign Keys in a table Primary Key field cannot contain duplicate values. All values must be unique Foreign Key field can contain duplicate value. (^12) Data in a relational database management system (RDBMS) is organized in the form of tables. (^13) A relational database is a collective set of multiple data sets organized by tables, records and columns. Relational database establish a well-defined relationship between database tables. Tables communicate and share information, which

Binary Types: Binary data types are used for storing data in binary formats.

Binary data types in a database can be using for storing photos, music files, etc. DATE TIME: Date time data types are used for describing date and time values for the field used in the table of a database. (^20) ASSESSMENT Fill in the blanks:

  1. A table is a set of data elements that is organized using a model of vertical Columns and horizontal Rows.
  2. A Field or column or attribute is a set of data values of a particular type, one for each row of the table.
  3. A Record or Row or Tuple represents a single, data item in a table.
  4. Datatype are used to identify which type of data we are going to store in the database.
  5. There are two ways to create a table.
  6. Field properties can be set in both the Design View and Wizard. Short Answer Questions
  7. In how many ways tables can be created in Base? Ans Two ways; Using design view and using wizard
  8. Why are data types used in DBMS /RDBMS? Ans Data types helps maintain data integrity in the table
  9. List datatypes available in Numeric Datatype? Ans Boolean, tinyint, smallint, integer, bigint, numeric, decimal, real, float, double
  10. List datatypes available in Alphanumeric Datatype? Ans Char, Varchar
  11. Define the structure of a table. Ans DDL commands define the structure of a table
  12. Differentiate between Tuples and Attributes of a table. Ans Tuples Attributes Rows or records are known as tuples Column or Fields are known as attributes

Tuples are horizontal Attributes are vertical

  1. Name different Binary data types. Ans Binary, VarBinary, LongVarBinary (^21) In Base, data is stored in tables which can be inserted, modified and removed using appropriate options. (^22) To insert the data in the table, follow the steps: Select the table > Double click on it. (^23) To edit the data either click on edit icon or double on the data in the cell of a table and modifications can be done. (^24) To remove the data from the table, follow the steps: Select the data > right click on selected data > select the Delete option (^25) To change the field properties table structure in design view has to be changed. To set the field properties, steps will be followed as: Select the table > Right click > Select the option Edit > the table Design View window will open (^26) AutoValue – if set to yes then field will get the auto numeric values. Length – By default length of the field is 10 but the size of the field can be set to maximum length. Default Value – A default value can be set for a field if user don’t provide any value while entering the values in the table. Format example – This property helps to set the format of the data entered in the field such as 91-222-333. (^27) Sorting means to arrange the data in either ascending order of descending order. (^28) Referential integrity is used to maintain accuracy and consistency of data in a relationship. In Base, data can be linked between two or more tables with the help of primary key and foreign key constraints. Referential integrity helps to avoid:  Adding records to a related table if there is no associated record available in the primary key table.  Changing values in a primary if any dependent records are present in associated table(s).  Deleting records from a primary key table if there are any matching related records available in associated table(s). (^29) A relationship refers to an association or connection between two or more tables. When you relate two tables, you don't need to enter the same data in separate tables. Relationships between tables helps to:  Save time as there is no need to enter the same data in separate tables.  Reduce data-entry errors.  Summarize data from related tables

4.What do you mean by Sorting? In how many ways it can be done? Ans Sorting is arranging the records in either ascending or descending order. It can be done in two ways either ascending or descending 5.Explain Referential Integrity with the help of an example. Ans Referential integrity is used to maintain the integrity of data based on the primary key of another table. Example: if there are two tables student with fields schoolno, name, class, section and result with fields resno, schoolno, exam, subject, marks then the field schoolno in result can become a foreign key with relation to the table students schoolno field. Hence the schoolno field in result can contain only those values which exist in the student table (^33) Query is to collect specific information from the pool of data. A query helps us join information from different tables and filter that information. (^34) Filtering means that the query uses criteria you provide to hide some data and present only what you want to see. (^35) Some RDBMS provide a graphical means to create queries, but most RDBMS do not do so. That’s where you use SQL (pronounced as “sequel”) or Structured Query Language. (^36) Query languages are computer languages used to make queries into databases and information systems. Queries are commands that are used to define the data structure and also to manipulate the data in the database. (^37) A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language(DML) command. (^38) To retrieve all the columns in a table the syntax is: SELECT * FROM ; (^39) To display the records containing the same type of values “WHERE” clause can be used with the Select SQL Command. To get details about the list of students whose favorite color is blue, you can use: select * from SDetails where Color=’Blue’; (^40) To view records in ascending order of RollNo, from the table the select statement will be: select * from SDetails order by “Rollno” ASC; (^41) You can add, modify or delete records using the Insert, Update and Delete commands. To type and execute SQL commands, click on Tools > SQL. (^42) Update statement is used for modifying records in a database. The general syntax of the update statement is as follows: UPDATE <table_name> SET <column_name> = value [, column_name = value ...] [WHERE ]; (^43) Assessment Fill in the blanks

  1. A Form helps the user to systematically store information in the database.
  2. A Form enables users to view, enter, and change data directly in database objects such as tables.
  3. SELECT statement retrieves zero or more rows from one or more database tables or database views.
  4. By default, data is arranged in Ascending order using ORDER BY clause.
  5. UPDATE statement is used for modifying records in a database.
  6. DELETE statement is used to remove one or more records in a Database. Short Answer Questions:
  7. Name DML commands. Ans SELECT, INSERT, UPDATE, DELETE
  8. What is the purpose of using queries? Ans Queries helps to define the data structure and also to manipulate the data in the database using command.
  9. Which clause of Select statement helps to display specific data? Ans WHERE
  10. Differentiate between Where and Orderby clause of SQL statements. Ans Where Order By Where clause is used with SELECT command to filter the records on the basis of some specific condition Order By clause is used in SELECT to display records arranged in ascending or descending order of any filed.
  11. State the purpose of Update Command with the help of an example. Ans Update command is used to change the value in a table. For Example if we want to change the marks of a student with rollno 5 then update command will be used. (^44) A form provides the user a systematic way of storing information into the database. It is an interface in a user specified layout that lets users to view, enter, and change data directly in database objects such as tables. (^45) To create a form, Click on Forms option located under Database section (^46) A report helps to display the data in a summarized manner. It is used to generate the overall work outcome in a clear format. You can create reports in the database. (^47) Fill in the blanks: