Download unit 2 notes of rdbms and more Lecture notes Computer science in PDF only on Docsity!
UNIT II
An Overview 2.1 PERSONAL DATABASES: Personal database management systems, such as Microsoft Access and Visual Fox Pro,are usually stored on user's desktop computer system or a client computer. These database packages are developed primarily for single- user applications. A server is a computer that accepts and services requestsfrom other computers,such as client computers. A server's resourcescould include the server's hard-diskdrive space,application programs on a server's hard-disk drivespace,application programs on a server's hard drive,data storedon the server's drive,or printers. A network is an infrastructure of hardware and software that enables computers to communicate with each other. i) Demand On Client and Network: If the client requires a small piece of data from the server’s large database, the server has to transmit the entire database to the client over the network. The client computer hardware must handle heavy demand, and the network must sustain heavy traffic in both directions. In network environment, the system response to various client requests depends on the speed of the network and the amount of traffic over it. ii) Table Locking: The personal database system assumes that no two transactions will happen at the same time on one table, which is known as optimistic locking. The tables are not locked by the database system. If one agent sells a seat for a basketball game and another agent tries to sell the same seat at the same time, the data base system will notify the second agent about the update on the table after his read but it will go ahead and let the second agent sell the seat anyway. iii) Client Failure: When a client is performing record insertions, deletions, or updates, those records are locked by that client and are not available to the other clients. If the client with all the record locks fails because of software or hardware malfunction or a power outage, the locked records stay locked. The database can get corrupted and needs to be repaired. To repair the database, all user have to log off during the repair.
If the database is not repairable, data can restored from the last backup, but the transactions since the last backup are lost and have to be reentered. iv) Transaction Processing: Personal databases, such as Microsoft access, do not have file- based transaction logging. Instead, transactions are logged in the client’s memory. If the client fails in the middle of a batch of transactions, some transactions are written to the database and same are not. The transaction log is lost, because it is not stored in a file. If a client writes a check to transfer money from a savings account to a checking account, the first transaction debits money from the savings account. 2.2 CLIENT/SERVER DATABASES: Client/Server databases, such as Oracle, run the DBMS as a process on the server and run a client database application on each other. The client application sends a request for data over the network to the server. i) Demand On Client and Network: The client does not store any database on its local drive;it receives only the requested data from the server. Data processing is performed on the server’s side. The demand at the client’s end is minimal. The clients request data from the server, and only requested data are send back via the network , which result in less traffic ii) Table Locking: In a client/server system, such as Oracle, when an agent reads a table to sell a seat for a basketball game, for example, it is locked totally or partly by the DBMS.
- SQLplus – The SQLplus environment is writing for command-line SOL queries to work with database objects such as tables, views, synonyms, and sequences.
- PL/SQL –PL/SQL is Oracle’s extension to SQL for creating procedural code to manipulate data.
- Developer Suite – This tool is used for developing database applications and includes: JDeveloper – a java development tool. Designer – to model business processes and generate enterprise applications. Forms Developer – a development tool for Internet and client/server-based environments. Oracle Reports – a report generation tool.
- Enterprise Manager – A tool for managing users and databases.Enterprise Manager uses the following tools: Storage Manager – to createand manage “tablespaces”. Instance manager – to start, stop or tune databases. Security manager – to create and manage users, profiles and roles. Warehouse Manager – to manage data warehousing applications. XML Database Manager – to render traditional database data as XML for e-business support. SQL Worksheet – to enter, edit, and execute SQLplus code or to run client-side scripts. iSQLplus – a web-based environment to execute SQL*plus code.
- Oracle Application Server(Oracle9iAS) – A tool for creatingaweb site thatallows users to access Oracle databases through web pages. It includes: Web Server 2.4 THE SQLPLUS ENVIRONMENT: When a user logs in to connect to the Oracle server, SQLplus provides the user with the SQL>prompt, where the user writes queries or commands. Features of SQL*plus include: 1.Accepts ad hoc entry of statements at the command line (i.e.,SQL>).
- Accepts SQL statements from files.
- Provides a line editor for modifying SOL queries.
- Provides environment, editor, format, execution, interaction, and file commands.
- Formats query results, and display reports on the screen.
- Controls environmental settings.
- Accesses local and remote databases.
2.5 STRUCTURED QUERY LANGUAGE(SQL):
ANSI and ISO Structured Query Language is a forth- generation, high-level, nonprocedural language. The SQL language uses English-like commands such as C, COBOL, or Visual Basic, which are procedural. Oracle9i uses the following types of SQL statements for command-line queries to communicate with the Oracle server from any tool or application:
- Data retrieval – retrieves data from the database. 2.Data Manipulation Language(DML) – inserts new rows, changes existing rows, and removes unwanted rows.
- Data Definition Language(DDL) – creates, changes, and removes a table’s structure.
- Transaction control – manages and changes logical transactions. Transactions are changes made to the data by DML statements that are grouped together.
- Data control Language (DCL) – gives and removesrightsto Oracle objects. 2.6 LOGGING IN TO SQLPLUS: In the windows environment, click start | programs | Oracle – Orahome92 | Application Development | SQL Plus. A Log on window will pop up. Enter your Username, Password, and HostString as provided by your Database Administrator. In a command-line environment such as DOS, type sqlplus [username [/password [@host/database]]] to log in. There are a couple of login problems. If you enter an incorrect user-name or password, you will receive the following error message from Oracle server: ORA-01017:invalid username/password; logon denied If there is a connectivity issue between your client PC and Oracle or the host string has an invalid entry. ORA-12154:TNS: could not resolve service name SQL queries are terminated using a semicolon(;) at the end or by typing a forward slash(/) on a new line.
2.7 SQL*PLUS COMMANDS:
2.7.1 SQL*plus file-related commands: COMMAND DESCRIPTION GET filename [.ext] START filename [.ext] @filename EDIT EDIT [filename [.ext] ] SAVE filename [.ext] REPLACE | Writers previously saved file to the buffer. The default extension is SQL. Writes SQL statements, not SQL plus commands. Runs a previously saved command from file. Same as START. Invokes the default editor (e.g., Note pad), and saves buffer contents in a file called afiedt.buf. Saves current buffer contents to a file with the option to replace or append. APPEND SPOOL [filename [.ext] |OFF Stores query results in a file.OFF closes the file, and OUT sends the file to the SQL SQLplus 1.A nonprocedurallanguage to communicate with the oracle server.
- ANSI standard.
- Key words cannot be abbreviated.
- Last statement is stored in the buffer.
- Statement manipulate data and table structures in the data base.
- Uses a termination character to execute the command immediately.
- An environment for executing SQL Statements.
- Oracle’s proprietary environment.
- Key words can be abbreviated.
- Commands are not stored in buffer.
- Commands do not allow manipulation of data in the database. 6.Commands do not need a termination character.
Buffer Formatted Report SQL*plus Server
|OUT]
Exit system printer. Leaves SQLplus environment. Commits current transaction 2.7.2 SQLplus editing commands: COMMAND DESCRIPTION APPEND text CHANGE / old / new CHANGE /text / CLEAR BUFFER DEL DEL n DEL m n INPUT INPUT text LIST LIST n LIST m n RUN N n text 0 text CLEAR SCREEN Adds text to the end of the current line. Changes old text to new text in the current line. Deletes text from the current line. Deletes all lines from the SQL statement. Deletes current line. Deletes line n. Deletes line m through n. Inserts an indefinite number of lines. Inserts a line of text. Lists all lines from the SQL buffer. Lists line n. Lists line m through n. Displays and runs an SQL statement in the buffer. Makes line N current. Replaces line n with text. Inserts a line before line 1. Clears screen. 2.8 ORACLE ERRORS AND ONLINE HELP: If you make a syntax error, Oracle will display an error message showing the line number and the error location on that line. Some errors are easy to find; some are not. To fix an error, always start at the line where the error is shown, but keep in mind that the error might not be on that line.
2.10 SQL*PLUS WORKSHEET:
The SQLplus worksheet is another environment available with Oracle’s Enterprise Manager. The SQLplus worksheet enables you to enter, edit, and execute SQLplus code.You can also run client-side scripts. START | [All] programs | Oracle – Orahome92 | Application Development | SQLplus Worksheet An enterprise Manager login screen is then displayed. The user logs in with username, password, a host string, just like logging into SQLplus. On a successful login, the leftside, a tool bar is displayed with connection, execute, command history, previous command, next command, and help icons from top to bottom, respectively. The SQLplus Worksheet screen has two horizontal halves. The user save input and output in separate files with the FILE menu and its options Save Input As… and Save Output As…, respectively. The input is stored in a file with the default extension. sql , and output is stored with the default extension .txt.
2.11 iSQLPlus: The third environment is web based and is called iSQLplus. To access it through a web browser, enter a URL as follows: http://machinename.domainname:port/isqlplus In this URL, machinename is your machine, but port number is not required in all versions.
A column allows NULL values and to specify if a column should have unique values only. A table and column names can be up to 30 characters long. In naming tables and columns, letters, numbers and special characters- $,_,# are allowed. It must begin with a letter and it is not case sensitive. Spaces and hyphens are not allowed in a table or column name. An Oracle server-reserved word cannot be used as a table or column name. Table name must be unique in a schema or user account. Valid Names Invalid Names STUDENT STUDENT_COURSE_REGISTRATION_TAB LE(more than 30 characters long) MAJOR_CODE MAJOR CODE (spaces not allowed) X CREATE (reserved word not allowed) PROJECT2000 PROJECT****2000 (special character * not allowed) STUDENT#REG#TA BLE #STUDENT (must start with a letter) 2.13 DATA TYPES A table is created each column in the table is assigned a data type. A data type specifies the type of data that will be stored in that column Data type also help to optimize storage space i)Varchar The VARCHAR2 type is a character data type to store variable- length alphanumeric data in a column. The default and minimum size is one character. The maximum allowable size is 4000 character in oracle9i. The size is specified within parentheses. Ex: VARCHAR2(20) If the data are smaller than the specified size. Only the data value is stored. And trailing spaces are not added to the value. If a value longer than the specified size is entered,however,an error is generated. The VARCHAR2 data type can also take CHAR or BYTE parameters. ii) Char The CHAR type is a character data type to store fixed-length alphanumeric data in a column.
The default and minimum size is one character. The maximum allowable size is 2000 character. The CHAR data type uses the storage more efficiently and processes data faster than the VARCHAR2 type. The CHAR data type can also take CHAR or BYTE parameter. Ex: PHONE, SOCIAL_SECURITY_NUMBER or MIDDLE_INITIAL columns iii) Number The NUMBER data type is used to store negative positive integer, fixed-decimal, and floating-point numbers. The NUMBER data type is used for any column that is going to be employed in mathematical calculations Ex: SALARY, COMMISSION, or PRICE When a number type is used for a column,its precision and scale can be specified. Precision is the total number of significant digits in the number, both to the left and to the right of the decimal point. The decimal point is not counted in specifying the precision. Range from 1 to 38. Scale is the total number of digits to the right of the decimal point. Range from -84 to 127. iv) Integer: An integer is a whole number without any decimal part. To define a column with integer values. Only the scale size is provided. For example to store the Employee Id 111, 112, 123 the data type for it would be NUMBER(3). v) Fixed-point: A fixed-point decimal number has a specific number of digits to the right of the decimal point. The price column has values in dollars and cents, which requires two decimal places. For example, the values like 2.95, 24.99 it is defined as NUMBER(4,2). vi) Floating-point: Floating-point decimal number has a variable number of decimal places. The decimal point may appear after any number of digits and it may not appear at all. To define such a column, do not specify the scale or precision along with the NUMBER type. For example, TAXRATE, INTEREST_RATE. vii) Date The DATE data type is used for storing date and time values.
2,14 CONSTRAINTS
Constraints enforce rules on tables. An oracle table can be created with the column names, data types and column sizes which are sufficient to populate them with actual data. The constraints help you to make your database with integrity. Whenever a row / record is inserted, updated or deleted from the table, a constraint must be satisfied for the operation to succeed. A table cannot be deleted if there are dependencies from other tables in the form of foreign keys. 2.14.1 Type of constraints
- Integrity constraints: Define both the primary key and the foreign key with the table and primary key it references.
- Value constraints: Define if NULL values are disallowed. If UNIQUE values are required and if only certain set of values are allowed in a column. 2.14.2 Naming a constraint Oracle identifies constraints with an internal or user-created name. for a user’s account, each constraint name must be unique A user cannot create constraints in two different tables with the same name. Syntax:
Here, table name is the name of the table where the constraint is being defined. column name is the name of the column to which the constraint applies and constraint name is an abbreviation used to identify the constraint type. Constraint Abbreviation PRIMARY KEY Pk FOREIGN KEY Fk UNIQUE uk CHECK Ck or cc NOT NULL nn 2.14.3 Defining a constraint A constraint can be created at the same time the table is created, or it can be added to the table afterward. There are two levels where a constraint is defined: - Column level: A column-level constraint references a single column and is defined along with the definition of the column. Any constraint can be defined at the column level except for a FOREIGN KEY and composite primary key constraint. Syntax: Column datatype [CONSTRAINT constraint_name]constraint_type, 2.Table level: A table-level constraint reference one or more columns and is defined separately from the definition of the columns. It is written after all columns are defined. All constraints can be defined at the table level except for the NOT NULL constraint. Syntax: [CONSTRAINT constraint_name]constraint_type(column,…), i) The PRIMARY KEY constraint: The PRIMARY KEY constraint is also known as the entity integrity constraint It creates a primary key for the table. A table can have only one primary key constraint. A column or combination of columns used as a primary key cannot have a null value, and it can only have unique values. At the column level constraint is defined by: Deptid NUMBER(2)CONSTRAINT dept_deptid_pk PRIMARY KEY, At the table level constraint is defined by: CONSTRAINT dept_deptid_pk PRIMARY KEY(Deptid), ii) The FOREIGN KEY constraint: The FOREIGN KEY constraint is also known as the referential integrity constraint. It uses a column or columns as a foreign key, and it establishes a relationship with the primary key of the same or another table. To establish a foreign key in a table, the other referenced table and its primary key must already exist. Foreign key and referenced primary key columns need not have the same name, but s foreign key value must match the value in the parent’s primary key value or be NULL. At the table level: CONSTRAINT student_facultyid_fk FOREIGN KEY(Facultyid) REFERENCES faculty(Facultyid),
Name VARCHAR2(15) CONSTRAINT faculty_name_ck CHECK(name is NOT NULL). vii) The DEFAULT Value (it’s not a constraint): The DEFAULT value ensures that a particular column will always have a value when a new row is inserted. The default value gets overwritten if a user enters another value. The default value is used if a null value is inserted. At the column level: State CHAR(2) DEFAULT ‘NJ’, 2.15 CREATING AN ORACLE TABLE A user creates an Oracle table in the SQLplus environment A Data Definition Language(DDL) SQL statement, CREATE TABLE, is used for table creation. 2.15.1 Syntax of CREATE TABLE statement is: CREATE TABLE [schema.] tablename (column1 datatype [CONSTRAINT constraint_name] constraint_type.., Column2 datatype [CONSTRAINT constraint_name] constraint_type, [CONSTRAINT constraint_name] constraint_type (column,….)…); Each column may have Zero,one or more constraint defined at the column level, the table level constraint are normally declared after all column definitions SQL is not case sensitive. Example: CREATE TABLE student (studid NUMBER(3), CONSTRAINT student_studid_pk PRIMARY KEY(studid), name VARCHAR2(30), age NUMBER(3) CONSTRAINT student_age_nn NOT NULL, address varchar2(50)); When the statement is executed and there are no syntax errors, a “Table Created” message will be displayed on the screen. The error messages are not very userfriendly. SQL> CREATE TABLE emplevel (LevelNo NUMBER(1), 2 LowSalary Number(6), 3 HighSalary Number(6), 4 Consraint emplevel_levelno_pk PRIMARY KEY (LevelNo)); CREATE TABLE emplevel (LevelNo NUMBER(1), ERROR at line 1: ORA-00922: missing or invalid option SQL> 3 3 HighSalary Number(6) SQL> A,
3* HighSalary Number(6) SQL> / CONSRAINT emplevel_levelno_pk PRIMARY KEY (LevelNo)) ERROR at line 4: ORA-00907: missing right parenthesis SQL> C/CONSRAINT/CONSTRAINT/ 4* CONSRAINT emplevel_levelno_pk PRIMARY KEY (LevelNo)) SQL> / Table Created The error is in line 3, and perform the following steps: 1 Go to line 3 2 Replace the character ) in line 3 with ), or append a comma (,) to the line 3 Execute the debugged statement using a slash (/) We can edit erroneous statement with the help of an alternate editor, such as notepad. To load an erroneous statement in Notepad and modify it, we can perform the following steps: 1 At the SQL> prompt, we type ED (or EDIT) to invoke Notepad. 2 We make required corrections to the script. 3 We save our statement and name as A:\CREATE, Notepad adds the extension .txt to the filename. To suppress Notepad’s default .txt extension, type the file name in a pair of double quotes, and use the extension .sql (e.g: “A:\CREATE.SQL”). 4 We exit Notepad to go back to the SQL* Plus environment. 5 We can run the saved statement with @ or the RUN command. Once the table is created the table structure is saved. More constraints can be added, more columns can be added and existing columns properties can be changed. 2.15.2 STORAGE clause in CREATE TABLE A CREATE TABLE statement may have an optional STORAGE clause. This clause is used to allocate initial disk space for the table at the time of creation with the INITIAL parameter and also to allocate additional space with the NEXT parameter in case the table runs out of allocated initial space. For example: CREATE TABLE sample (id NUMBER(3), Name VARCHAR2(25)) TABLESPACE CIS_DATA STORAGE (INITIAL 1M NEXT 100K) PCTFREE 20; The STORAGE clause allocates 1 megabyte initially on tablespace CIS_DATA, and 100 kilobytes as additional space on the same table space.. The INITIAL and NEXT parameter use value in K (kilobyte) or M (megabytes).
