--Your friends at LectureNotes

Note for Database Management System - DBMS By ANNA SUPERKINGS

  • Database Management System - DBMS
  • Note
  • Information Technology Engineering
  • 3 Topics
  • 4 Offline Downloads
  • Uploaded 1 year ago
Anna Superkings
Anna Superkings
0 User(s)
Download PDFOrder Printed Copy

Share it with your friends

Leave your Comments

Text from page-1

SQL Overview course material (Lecture Notes) Structured Query Language The standard for relational database management systems (RDBMS) SQL-92 and SQL-99 Standards – Purpose: Specify syntax/semantics for data definition and manipulation Define data structures Enable portability Specify minimal (level 1) and complete (level 2) standards Allow for later growth/enhancement to standard Catalog A set of schemas that constitute the description of a database Schema The structure that contains descriptions of objects created by a user (base tables, views, constraints) Data Definition Language (DDL) Commands that define a database, including creating, altering, and dropping tables and establishing constraints Data Manipulation Language (DML) Commands that maintain and query a database Data Control Language (DCL) Commands that control a database, including administering privileges and committing data CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2

Text from page-2

course material (Lecture Notes) SQL Database Definition Data Definition Language (DDL) Major CREATE statements: CREATE SCHEMA – defines a portion of the database owned by a particular user CREATE TABLE – defines a table and its columns CREATE VIEW – defines a logical table from one or more views Other CREATE statements: ASSERTION, DOMAIN CHARACTER SET, COLLATION, TRANSLATION, In SQL, a VIEW is a virtual relation based on the result-set of a SELECT statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. In some cases, we can modify a view and present the data as if the data were coming from a single table. Syntax: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name condition WHERE SQL – Relations, Tables & Views CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2

Text from page-3

course material (Lecture Notes) When we say Relation, it could be a Table or a View. There are three kind of relations: 1. Stored relations tables We sometimes use the term ―base relation‖ or ―base table‖ 1. Virtual relations views 2. Temporary results SQL – Create View Example: Create a view with title and year and made by Paramount studio. Movie (title, year, length, inColor, studioName, producerC#) CREATE VIEW ParamountMovie AS SELECT title,year FROM Movie WHERE studioName = ‗Paramount‘; SQL – Querying View A view could be used from inside a query, a stored procedure, or from inside another view. By adding functions, joins, etc., to a view, it allows us to present exactly the data we want to the user. SELECT title FROM ParamountMovie WHERE year = ‗1979‘; Have same result as SELECT title FROM Movie WHERE studioName = ‗Paramount‘ AND year = ‗1979‘; Example: Movie (title, year, length, inColor, studioName, producerC#) MovieExec (name, address, cert#, netWorth) CREATE VIEW MovieProd AS SELECT title, name FROM Movie, MovieExec WHERE producerC# = cert#; SELECT name FROM MovieProd WHERE title = ‗Gone With the Wind‘; Same result as query from tables SELECT name FROM Movie, MovieExec WHERE producerC# = cert# AND title = ‗The War Of the World‘; Data Types in SQL CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2

Text from page-4

course material (Lecture Notes) String types CHAR(n) – fixed-length character data, n characters long Maximum length = 2000 bytes VARCHAR2(n) – variable length character data, maximum 4000 bytes LONG – variable-length character data, up to 4GB. Maximum 1 per table Numeric types NUMBER(p,q) – general purpose numeric data type INTEGER(p) – signed integer, p digits wide FLOAT(p) – floating point in scientific notation with p binary digits precision Date/time type DATE – fixed-length date/time in dd-mm-yy form CS6302 DATABASE MANAGEMENT SYSTEMS UNIT-2

Lecture Notes