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

authorization in dbms with sql as reference, Essays (university) of Database Management Systems (DBMS)

Bharat Ratna Dr. B. R. Ambedkar UniversityDatabase Management Systems (DBMS)

ppt for database management systems

Typology: Essays (university)

2017/2018

Uploaded on 08/27/2018

cksjfhv
cksjfhv ๐Ÿ‡ฎ๐Ÿ‡ณ

5

(1)

1 document

1 / 27

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Built-in Data Types in SQL
โ€ขdate: Dates, containing a (4 digit) year, month and
date
โ€“Example: date โ€˜2005-07-27โ€™
โ€ขtime: Time of day, in hours, minutes and seconds.
โ€“Example: time โ€˜09:00:30โ€™ time
โ€˜09:00:30.75โ€™
โ€ขtimestamp: date plus time of day
โ€“Example: timestamp โ€˜2005-07-27 09:00:30.75โ€™
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b

Partial preview of the text

Download authorization in dbms with sql as reference and more Essays (university) Database Management Systems (DBMS) in PDF only on Docsity!

Built-in Data Types in SQL

  • (^) date: Dates, containing a (4 digit) year, month and

date

  • (^) Example: date โ€˜2005-07-27โ€™
  • (^) time: Time of day, in hours, minutes and seconds.
  • (^) Example: time โ€˜09:00:30โ€™ time
  • (^) timestamp : date plus time of day
    • (^) Example: timestamp โ€˜2005-07-27 09:00:30.75โ€™

Date and Time types in SQL

โ€ข current_date , current_time,

current_timestamp, localtimestamp

  • (^) SQL functions

Index Creation

  • (^) create table student ( ID varchar (5), name varchar (20) not null , dept_name varchar (20), tot_cred numeric (3,0) default 0, primary key ( ID ))
  • (^) create index studentID_index on student ( ID )

Index Creation

  • (^) Indices are data structures used to speed up access to records with specified values for index attributes - (^) e.g. **select *** from student where ID = โ€˜12345โ€™ can be executed by using the index to find the required record, without looking at all records of student

User-Defined Types

  • (^) Assigning Pounds type value to Dollars type variable is an error will be reported by the system.
  • (^) Type casting
    • (^) cast ( department.budget to numeric(12,2) )
  • (^) drop type and alter type clauses to drop or modify types

Domains

  • (^) create domain construct in SQL-92 creates user- defined domain types - (^) create domain person_name char (20) not null ;
  • (^) Types and domains are similar. Domains can have constraints, such as not null , specified on them.

Large-Object Types

  • (^) Large objects (photos, videos, CAD files, etc.) are stored as a large object : - (^) blob : binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system) - (^) clob : character large object -- object is a large collection of character data - (^) When a query returns a large object, a pointer is returned rather than the large object itself.

Create table extensions

  • (^) create table temp_instructor like instructor ;
    • (^) temp_instructor has the same schema as instructor
  • (^) create table t1 as (select * from instructor where dept_name =โ€˜Musicโ€™) with data; By default, the names and data types of the columns are inferred from the query result.

Authorization

  • (^) Forms of authorization on parts of the database
    • (^) Select - allows reading, but not modification of data.
    • (^) Insert - allows insertion of new data, but not modification of existing data.
    • (^) Update - allows modification, but not deletion of data.
    • (^) Delete - allows deletion of data.

Grant Privileges

  • (^) A user who creates a new relation is given all privileges on that relation automatically.
  • (^) The grant statement is used to confer authorization grant on to
  • (^) is:
    • (^) a user-id
    • (^) public , which allows all valid users the privilege granted

Privileges in SQL

  • (^) select: allows read access to relation, or the ability to query using the view - Example: grant users^ U 1

, U

2 , and U 3 select authorization on the instructor relation: grant select on instructor to U 1

, U

2

, U

3

  • (^) insert : the ability to insert tuples
  • (^) update : the ability to update using the SQL update statement

Grant Privileges

  • (^) delete : the ability to delete tuples.
  • (^) all privileges : used as a short form for all the allowable privileges
  • (^) grant update ( budget ) on department to Amith ;
  • (^) SQL does not permit authorization on specific tuples of a relation.

Authorization Graph

Granting update authorization on Teaches

  • (^) A user has an authorization if and only if there is a path for the root down to the node representing the user

Revoking Authorization in SQL

  • (^) The revoke statement is used to revoke authorization. revoke on from
  • (^) Example: revoke select on branch from U 1

, U

2

, U

3

  • (^) <privilege-list> may be all to revoke all privileges the revokee may hold.