User:Avestaconsulting
From Wikipedia, the free encyclopedia
The purpose for this page is to make wise knowledge available to everyone
Contents |
[edit] D0178B
Technically D0178B is a guide line for avionic software lifecycle. In reality it is a restricted requirement. D0178B is a software development and QA guide line for Avionics industry. First version D0178 was a guideline covering the basic life cycle of an Avionic Software Development. The second version D0178A added software component testing to obtain quality. The current version D0178B enhanced the avionic software quality by adding planning, continuous monitoring, and testing for real-world conditions.
The three basic D0178B process are as follow: planning – development – correctness. - planning (Configuration management, Quality Assurance, Development, Testing). - Development (Requirement, design, implementation). - Correctness: Testing / verification and Certification.
What are critical to D0178B attributes - integration testing, detailed planning, tractability, constancy, independent reviews.
DER is required to certify commercial RIUs. It stands for Designated Engineering Representatives who is a liaison between manufacturer and FAA
What is D0178B and D0-254? These are certification standards for airborne equipment. D0178B for software & D0-254 for hardware.
What are the D0-178B Five Key Plans: 1. PSAC: Plan for the software aspect of certification. 2. SQAP: Software Quality Assurance Plan. 3. SCMP: Configuration Management for software. 4. SWDP: Software Development Plan. 5. SWVP: Software Verification Plan.
Software Verification Plan Address Reviews and acceptance, Describe traceability, Cover aspect of testing and integration, include test environment and tools, discuss regression and re-verification Functional Test (Black Box): Normal Range Tests (White Box): Robustness Tests (White Box): Structural Coverage Test (White Box):
Criticality Level Pyramid:
Level A: Catastrophic Level B: Hazardous/Severe Level C: Major Level D: Minor Level E: No Effect.
[edit] A 16 Bit Word
Modern computers have a natural word size of 16, 32, or 64 bits. The key word "word" is the natural measure for data usage in any computer design. A word is group of bits grouped together for machine processing. The number of bits in a word is also referred to by word size or length. In computer science a byte (pronounced "bite") is a unit measurement to store information. It is usually consistent of 8 bits.
[edit] Asynchronous communication
1. In asynchronous communication the transmits & receive clocks are independent.
2. Simple interface (limited data rate usually < 64 Kbps (kilo bits per second).
3. No clock sent (Tx & RX have their own clocks).
4. Requires stop and start bits which increase overhead.
[edit] Synchronous communication
1. In synchronous communication the transmit & Receive clocks are synchronized.
2. The interface is more complex (high data rate of up to 10 Gbps.
3. Clock sent with Data
The principle difference between the synchronous and asynchronous modes of transmission is that in the synchronous case, the receiver uses a clock which is synchronized to the transmitter clock. Synchronous transmission has the advantage that the timing information is accurately aligned to the received data, allowing operation at much higher data rates. It also has the advantage that the receiver tracks any clock drift which may arise (for instance due to temperature variation). The penalty is however a more complex interfaces design, and potentially a more difficult interface to configure
[edit] UML Unified Modeling Language
What are the three components of the UML?
The use case diagrams represent the functional model of a system. Use Case Diagrams.
The objects, their attbibutes, operations, communication protocals, and associations with other objects are described using an object Model. Class Diagrams.
The internal behavior of the system is shown using a dynamic model. Sequence Diagrams, Activity Diagrams and State Machine Diagrams.
There are other Diagrams used in the UML to describe a system:
Structure Diagrams emphasize what things must be in the system being modeled:
•Class diagram
•Component diagram
•Object diagram
•Composite structure diagram
•Deployment diagram
•Package diagram
Behavior Diagrams emphasize what must happen in the system being modeled:
•Activity diagram
•Use case diagram
•State Machine diagram
Interaction Diagrams, a subset of behavior diagrams, emphasize the flow of control and data among the things in the system being modeled:
•Sequence diagram
•Collaboration (UML 1.x)/Communication diagram (UML 2.0)
•Interaction overview diagram (UML 2.0)
•Timing diagram (UML 2.0)
[edit] Object-Oriented Programming Concepts
What Is an Object? : An object is a software bundle of related variables and methods. Software objects are often used to model real-world objects you find in everyday life.
What Is a Message? : Software objects interact and communicate with each other using messages.
What Is a Class? : A class is a blueprint or prototype that defines the variables and the methods common to all objects of a certain kind.
What Is Inheritance? : A class inherits state and behavior from its superclass. Inheritance provides a powerful and natural mechanism for organizing and structuring software programs.
What Is an Interface? : An interface is a contract in the form of a collection of method and constant declarations. When a class implements an interface, it promises to implement all of the methods declared in that interface.
Object-oriented programming
In computer science, object-oriented programming, OOP for short, is a computer programming paradigm. The idea behind object-oriented programming is that a computer program may be seen as comprising a collection of individual units, or objects, that act on each other, as opposed to a traditional view in which a program may be seen as a collection of functions, or simply as a list of instructions to the computer. Each object is capable of receiving messages, processing data, and sending messages to other objects. Each object can be viewed as an independent little machine or actor with a distinct role or responsibility. Object-oriented programming is claimed to promote greater flexibility and maintainability in programming, and is widely popular in large-scale software engineering. Furthermore, proponents of OOP claim that OOP is easier to learn for those new to computer programming than previous approaches, and that the OOP approach is often simpler to develop and to maintain, lending itself to more direct analysis, coding, and understanding of complex situations and procedures than other programming methods. Critics dispute this, at least for some domains (industries).
Fundamental concepts
Object-oriented programming (OOP) emphasizes the following concepts:
• Class — the unit of definition of data and behavior (functionality) for some kind-of-thing. For example, the 'class of Dogs' might be a set which includes the various breeds of dogs. A class is the basis of modularity and structure in an object-oriented computer program. A class should typically be recognizable to a non-programmer familiar with the problem domain, and the code for a class should be (relatively) self-contained and independent (as should the code for any good pre-OOP function). With such modularity, the structure of a program will correspond to the aspects of the problem that the program is intended to solve. This simplifies the mapping to and from the problem and program.
• Object — an instance of a class, an object (for example, "Lassie" the Dog) is the run-time manifestation (instantiation) of a particular exemplar of a class. (For the class of dogs which contains breed types, an acceptable exemplar would only be the subclass 'collie'; "Lassie" would then be an object in that subclass.) Each object has its own data, though the code within a class (or a subclass or an object) may be shared for economy.
• Method (also known as message) — how code can use an object of some class. A method is a form of subroutine operating on a single object. Methods may be divided into queries returning the current state and commands changing it: a Dog could have a query Age to say how old it is, and command chase (Rabbit target) to start it chasing a rabbit. A method may also do both, but some authorities (e.g. Bertrand Meyer) recommend they be kept separate. Sometimes access to the data of an object is restricted to the methods of its class.
o A member of a class or object is a method or a data item describing the state of an object. In some languages the general term is feature.
• Inheritance — a mechanism for creating subclasses, inheritance provides a way to define a (sub)class as a specialization or subtype or extension of a more general class: Dog is a subclass of Canidae, and Collie is a subclass of the (sub)class Dog. A subclass inherits all the members of its superclass(es), but it can extend their behaviour and add new members. Inheritance is the "is-a" relationship: a Dog is a Canidae. This is in contrast to composition, the "has-a" relationship: a Dog has a mother (another Dog) and has a father, etc.
o Multiple inheritance – a Dog is both a Pet and a Canidae – is not always supported, as it can be hard both to implement and to use well.
• Encapsulation — ensuring that code outside a class sees only functional details of that class, but not implementation details. The latter are liable to change, and could allow a user to put an object in an inappropriate state. Encapsulation is achieved by specifying which classes may use the members of an object. The result is that each object exposes to any class a certain interface — those members accessible to that class. For example, an interface can ensure that puppies can only be added to an object of the class Dog by code in that class. Members are often specified as public, protected and private, determining whether they are available to all classes, sub-classes or only the defining class. Some languages go further: Java uses the protected keyword to restrict access also to classes in the same package, C# and VB.NET reserve some members to classes in the same assembly using keywords internal (C#) or Friend (VB.NET), and Eiffel allows one to specify which classes may access any member.
• Abstraction — the ability of a program to ignore the details of an object's (sub)class and work at a more generic level when appropriate; For example, "Lassie" the Dog may be treated as a Dog much of the time, but when appropriate she is abstracted to the level of Canidae (superclass of Dog) or Carnivora (superclass of Canidae), and so on.
• Polymorphism — polymorphism is behavior that varies depending on the class in which the behavior is invoked, that is, two or more classes can react differently to the same message. For example, if Dog is commanded to speak this may elicit a Bark; if Pig is commanded to speak this may elicit a Grunt. An object-based language is a language that has most of the properties of an object-oriented language, but may lack some. For example Visual Basic lacks inheritance, while a Prototype-based programming language relies on prototypes instead of classes to create objects.
Object oriented technology is based on a few simple concepts that, when combined, produce significant improvements in software construction. Unfortunately, the basic concepts of the technology often get lost in the excitement of advanced features and advantageous features. The basic characteristics of the OOM are explained ahead. Characteristics of Object Oriented Technology:
- Identity
- Classification
- Polymorphism
- Inheritance
Identity: The term Object Oriented means that we organize the software as a collection of discrete objects. An object is a software package that contains the related data and the procedures. Although objects can be used for any purpose, they are most frequently used to represent real-world objects such as products, customers and sales orders. The basic idea is to define software objects that can interact with each other just as their real world counterparts do, modeling the way a system works and providing a natural foundation for building systems to manage that business.
Classification: In principle, packaging data and procedures together makes perfect sense. In practice, it raises an awkward problem. Suppose we have many objects of the same general type- for example a thousand product objects, each of which could report its current price. Any data these objects contained could easily be unique for each object. Stock number, price, storage dimensions, stock on hand, reorder quantity, and any other values would differ from one product to the next. But the methods for dealing with these data might well be the same. Do we have to copy these methods and duplicate them in every object? No, this would be ridiculously inefficient. All object-oriented languages provide a simple way of capturing these commonalties in a single place. That place is called a class. The class acts as a kind of template for objects of similar nature.
Polymorphism: Polymorphism is a Greek word meaning ¡§many forms¡¨. It is used to express the fact that the same message can be sent to many different objects and interpreted in different ways by each object. For example, we could send the message "move" to many different kinds of objects. They would all respond to the same message, but they might do so in very different ways. The move operation will behave differently for a window and differently for a chess piece.
Inheritance: Inheritance is the sharing of attributes and operations among classes on a hierarchical relationship. A class can be defined as a generalized form and then it specialized in a subclass. Each subclass inherits all the properties of its superclass and adds its own properties in it. For example, a car and a bicycle are subclasses of a class road vehicle, as they both inherits all the qualities of a road vehicle and add their own properties to it.
[edit] Introduction SQL
SQL (Structured Query Language) is a database sublanguage for querying and modifying relational databases. It was developed by IBM Research in the mid 70's and standardized by ANSI in 1986. The Relational Model defines two root languages for accessing a relational database -- Relational Algebra and Relational Calculus. Relational Algebra is a low-level, operator-oriented language. Creating a query in Relational Algebra involves combining relational operators using algebraic notation. Relational Calculus is a high-level, declarative language. Creating a query in Relational Calculus involves describing what results are desired. SQL is a version of Relational Calculus. The basic structure in SQL is the statement. Semicolons separate multiple SQL statements.
There are 3 basic categories of SQL Statements:
SQL-Data Statements
-- query and modify tables and columns
SELECT Statement -- query tables and views in the database
INSERT Statement -- add rows to tables
UPDATE Statement -- modify columns in table rows
DELETE Statement -- remove rows from tables
SQL-Transaction Statements
-- control transactions
COMMIT Statement -- commit the current transaction
ROLLBACK Statement -- roll back the current transaction
SQL-Schema Statements
-- maintain schema (catalog)
CREATE TABLE Statement -- create tables
CREATE VIEW Statement -- create views
DROP TABLE Statement -- drop tables
DROP VIEW Statement -- drop views
GRANT Statement -- grant privileges on tables and views to other users
REVOKE Statement -- revoke privileges on tables and views from other users
SQL-Data Statements SQL-Data Statements perform query and modification on database tables and columns. This subset of SQL is also called the Data Manipulation Language for SQL (SQL DML). Contents: SELECT Statement -- query tables and views in the database SELECT Statement Basics SELECT Clause FROM Clause WHERE Clause Extended Query Capabilities ORDER BY Clause Value Expressions Joining Tables Subqueries Grouping Queries (GROUP BY) Aggregate Queries (Set Functions) Union Queries (UNION) SQL Modification Statements INSERT Statement -- add rows to tables VALUES Clause UPDATE Statement -- modify columns in table rows SET Clause DELETE Statement -- remove rows from tables
Joining Tables The FROM clause allows more than 1 table in its list, however simply listing more than one table will very rarely produce the expected results. The rows from one table must be correlated with the rows of the others. This correlation is known as joining. An example can best illustrate the rationale behind joins. The following query: SELECT * FROM sp, p Produces: sno pno qty pno descr color S1 P1 NULL P1 Widget Blue S1 P1 NULL P2 Widget Red S1 P1 NULL P3 Dongle Green S2 P1 200 P1 Widget Blue S2 P1 200 P2 Widget Red S2 P1 200 P3 Dongle Green S3 P1 1000 P1 Widget Blue S3 P1 1000 P2 Widget Red S3 P1 1000 P3 Dongle Green S3 P2 200 P1 Widget Blue S3 P2 200 P2 Widget Red S3 P2 200 P3 Dongle Green Each row in sp is arbitrarily combined with each row in p, giving 12 result rows (4 rows in sp X 3 rows in p.) This is known as a cartesian product. A more usable query would correlate the rows from sp with rows from p, for instance matching on the common column -- pno: SELECT * FROM sp, p WHERE sp.pno = p.pno This produces: sno pno qty pno descr color S1 P1 NULL P1 Widget Blue S2 P1 200 P1 Widget Blue S3 P1 1000 P1 Widget Blue S3 P2 200 P2 Widget Red Rows for each part in p are combined with rows in sp for the same part by matching on part number (pno). In this query, the WHERE Clause provides the join predicate, matching pno from p with pno from sp. The join in this example is known as an inner equi-join. equi meaning that the join predicate uses = (equals) to match the join columns. Other types of joins use different comparison operators. For example, a query might use a greater-than join. The term inner means only rows that match are included. Rows in the first table that have no matching rows in the second table are excluded and vice versa (in the above join, the row in p with pno P3 is not included in the result.) An outer join includes unmatched rows in the result. See Outer Join below.
Outer Joins An inner join excludes rows from either table that don't have a matching row in the other table. An outer join provides the ability to include unmatched rows in the query results. The outer join combines the unmatched row in one of the tables with an artificial row for the other table. This artificial row has all columns set to null. The outer join is specified in the FROM clause and has the following general format: table-1 { LEFT | RIGHT | FULL } OUTER JOIN table-2 ON predicate-1 predicate-1 is a join predicate for the outer join. It can only reference columns from the joined tables. The LEFT, RIGHT or FULL specifiers give the type of join: LEFT -- only unmatched rows from the left side table (table-1) are retained RIGHT -- only unmatched rows from the right side table (table-2) are retained FULL -- unmatched rows from both tables (table-1 and table-2) are retained Outer join example: SELECT pno, descr, color, sno, qty FROM p LEFT OUTER JOIN sp ON p.pno = sp.pno Subqueries Subqueries are an identifying feature of SQL. It is called Structured Query Language because a query can nest inside another query. There are 3 basic types of subqueries in SQL: Predicate Subqueries -- extended logical constructs in the WHERE (and HAVING) clause. Scalar Subqueries -- standalone queries that return a single value; they can be used anywhere a scalar value is used. Table Subqueries -- queries nested in the FROM clause. All subqueries must be enclosed in parentheses. Predicate Subqueries Predicate subqueries are used in the WHERE (and HAVING) clause. Each is a special logical construct. Except for EXISTS, predicate subqueries must retrieve one column (in their select list.) IN Subquery The IN Subquery tests whether a scalar value matches the single query column value in any subquery result row. It has the following general format: value-1 [NOT] IN (query-1) Using NOT is equivalent to: NOT value-1 IN (query-1) For example, to list parts that have suppliers: SELECT * FROM p WHERE pno IN (SELECT pno FROM sp) pno descr color P1 Widget Blue P2 Widget Red The Self Join example in the previous subsection can be expressed with an IN Subquery: SELECT DISTINCT pno FROM sp a WHERE pno IN (SELECT pno FROM sp b WHERE a.sno <> b.sno) pno P1 Note that the subquery where clause references a column in the outer query (a.sno). This is known as an outer reference. Subqueries with outer references are sometimes known as correlated subqueries. Quantified Subqueries A quantified subquery allows several types of tests and can use the full set of comparison operators. It has the following general format: value-1 {=|>|<|>=|<=|<>} {ANY|ALL|SOME} (query-1) The comparison operator specifies how to compare value-1 to the single query column value from each subquery result row. The ANY, ALL, SOME specifiers give the type of match expected. ANY and SOME must match at least one row in the subquery. ALL must match all rows in the subquery, or the subquery must be empty (produce no rows). For example, to list all parts that have suppliers: SELECT * FROM p WHERE pno =ANY (SELECT pno FROM sp) pno descr color P1 Widget Blue P2 Widget Red A self join is used to list the supplier with the highest quantity of each part (ignoring null quantities): SELECT * FROM sp a WHERE qty >ALL (SELECT qty FROM sp b
WHERE a.pno = b.pno
AND a.sno <> b.sno
AND qty IS NOT NULL)
sno pno qty S3 P1 1000 S3 P2 200 EXISTS Subqueries The EXISTS Subquery tests whether a subquery retrieves at least one row, that is, whether a qualifying row exists. It has the following general format EXISTS(query-1) Any valid EXISTS subquery must contain an outer reference. It must be a correlated subquery. Note: the select list in the EXISTS subquery is not actually used in evaluating the EXISTS, so it can contain any valid select list (though * is normally used). To list parts that have suppliers: SELECT * FROM p WHERE EXISTS(SELECT * FROM sp WHERE p.pno = sp.pno) pno descr color P1 Widget Blue P2 Widget Red Scalar Subqueries The Scalar Subquery can be used anywhere a value can be used. The subquery must reference just one column in the select list. It must also retrieve no more than one row. When the subquery returns a single row, the value of the single select list column becomes the value of the Scalar Subquery. When the subquery returns no rows, a database null is used as the result of the subquery. Should the subquery retreive more than one row, it is a run-time error and aborts query execution. A Scalar Subquery can appear as a scalar value in the select list and where predicate of an another query. The following query on the sp table uses a Scalar Subquery in the select list to retrieve the supplier city associated with the supplier number (sno column in sp): SELECT pno, qty, (SELECT city FROM s WHERE s.sno = sp.sno) FROM sp pno qty city P1 NULL Paris P1 200 London P1 1000 Rome P2 200 Rome The next query on the sp table uses a Scalar Subquery in the where clause to match parts on the color associated with the part number (pno column in sp): SELECT * FROM sp WHERE 'Blue' = (SELECT color FROM p WHERE p.pno = sp.pno) sno pno qty S1 P1 NULL S2 P1 200 S3 P1 1000 Note that both example queries use outer references. This is normal in Scalar Subqueries. Often, Scalar Subqueries are Aggregate Queries. Table Subqueries Table Subqueries are queries used in the FROM clause, replacing a table name. Basically, the result set of the Table Subquery acts like a base table in the from list. Table Subqueries can have a correlation name in the from list. They can also be in outer joins. The following two queries produce the same result: SELECT p.*, qty FROM p, sp WHERE p.pno = sp.pno AND sno = 'S3' pno descr color qty P1 Widget Blue 1000 P2 Widget Red 200 SELECT p.*, qty FROM p, (SELECT pno, qty FROM sp WHERE sno = 'S3') WHERE p.pno = sp.pno pno descr color qty P1 Widget Blue 1000 P2 Widget Red 200 Grouping Queries A Grouping Query is a special type of query that groups and summarizes rows. It uses the GROUP BY Clause. A Grouping Query groups rows based on common values in a set of grouping columns. Rows with the same values for the grouping columns are placed in distinct groups. Each group is treated as a single row in the query result. Even though a group is treated as a single row, the underlying rows can be subject to summary operations known as Set Functions whose results can be included in the query. The optional HAVING Clause supports filtering for group rows in the same manner as the WHERE clause filters FROM rows. For example, grouping the sp table on the pno column produces 2 groups: sno pno qty
S1 P1 NULL 'P1' Group S2 P1 200 S3 P1 1000 S3 P2 200 'P2' Group The P1 group contains 3 sp rows with pno='P1' The P2 group contains a single sp row with pno='P2' Nulls get special treatment by GROUP BY. GROUP BY considers a null as distinct from every other null. Each row that has a null in one of its grouping columns forms a separate group. Grouping the sp table on the qty column produces 3 groups: sno pno qty
S1 P1 NULL NULL Group S2 P1 200 200 Group S3 P2 200 S3 P1 1000 1000 Group The row where qty is null forms a separate group. GROUP BY Clause GROUP BY is an optional clause in a query. It follows the WHERE clause or the FROM clause if the WHERE clause is missing. A query containing a GROUP BY clause is a Grouping Query. The GROUP BY clause has the following general format: GROUP BY column-1 [, column-2] ... column-1 and column-2 are the grouping columns. They must be names of columns from tables in the FROM clause; they can't be expressions. GROUP BY operates on the rows from the FROM clause as filtered by the WHERE clause. It collects the rows into groups based on common values in the grouping columns. Except nulls, rows with the same set of values for the grouping columns are placed in the same group. If any grouping column for a row contains a null, the row is given its own group. For example, SELECT pno FROM sp GROUP BY pno pno P1 P2 In Grouping Queries, the select list can only contain grouping columns, plus literals, outer references and expression involving these elements. Non-grouping columns from the underlying FROM tables cannot be referenced directly. However, non-grouping columns can be used in the select list as arguments to Set Functions. Set Functions summarize columns from the underlying rows of a group. Set Functions Set Functions are special summarizing functions used with Grouping Queries and Aggregate Queries. They summarize columns from the underlying rows of a group or aggregate. Using the Group By example from above, grouping the sp table on the pno column: sno pno qty
S1 P1 NULL 'P1' Group S2 P1 200 S3 P1 1000 S3 P2 200 'P2' Group A Set Function can compute the total quantities for each group: sno pno qty
qty total S1 P1 NULL 'P1' Group 1200 S2 P1 200 S3 P1 1000 S3 P2 200 'P2' Group 200
Null columns are ignored in computing the summary. The Set Function -- SUM, computes the arithmetic sum of a numeric column in a set of grouped/aggregate rows. For example, SELECT pno, SUM(qty) FROM sp GROUP BY pno pno P1 1200 P2 200 Set Functions have the following general format: set-function ( [DISTINCT|ALL] column-1 ) set-function is: COUNT -- count of rows SUM -- arithmetic sum of numeric column AVG -- arithmetic average of numeric column; should be SUM()/COUNT(). MIN -- minimum value found in column MAX -- maximum value found in column The result of the COUNT function is always integer. The result of all other Set Functions is the same data type as the argument. The Set Functions skip columns with nulls, summarizing non-null values. COUNT counts rows with non-null values, AVG averages non-null values, and so on. COUNT returns 0 when no non-null column values are found; the other functions return null when there are no values to summarize. A Set Function argument can be a column or an scalar expression. The DISTINCT and ALL specifiers are optional. ALL specifies that all non-null values are summarized; it is the default. DISTINCT specifies that distinct column values are summarized; duplicate values are skipped. Note: DISTINCT has no effect on MIN and MAX results. COUNT also has an alternate format: COUNT(*) ... which counts the underlying rows regardless of column contents. Set Function examples: SELECT pno, MIN(sno), MAX(qty), AVG(qty), COUNT(DISTINCT sno) FROM sp GROUP BY pno pno P1 S1 1000 600 3 P2 S3 200 200 1 SELECT sno, COUNT(*) parts FROM sp GROUP BY sno sno parts S1 1 S2 1 S3 2 HAVING Clause The HAVING Clause is associated with Grouping Queries and Aggregate Queries. It is optional in both cases. In Grouping Queries, it follows the GROUP BY clause. In Aggregate Queries, HAVING follows the WHERE clause or the FROM clause if the WHERE clause is missing. The HAVING Clause has the following general format: HAVING predicate Like the WHERE Clause, HAVING filters the query result rows. WHERE filters the rows from the FROM clause. HAVING filters the grouped rows (from the GROUP BY clause) or the aggregate row (for Aggregate Queries). predicate is a logical expression referencing grouped columns and set functions. It has the same restrictions as the select list for Grouping Queries and Aggregate Queries. If the Having predicate evaluates to true for a grouped or aggregate row, the row is included in the query result, otherwise, the row is skipped (not included in the query result). For example, SELECT sno, COUNT(*) parts FROM sp GROUP BY sno HAVING COUNT(*) > 1 sno parts S3 2 Aggregate Queries An Aggregate Query can use Set Functions and a HAVING Clause. It is similar to a Grouping Query except there are no grouping columns. The underlying rows from the FROM and WHERE clauses are grouped into a single aggregate row. An Aggregate Query always returns a single row, except when the Having clause is used. An Aggregate Query is a query containing Set Functions in the select list but no GROUP BY clause. The Set Functions operate on the columns of the underlying rows of the single aggregate row. Except for outer references, any columns used in the select list must be arguments to Set Functions. See Set Functions above. An aggregate query may also have a Having clause. The Having clause filters the single aggregate row. If the Having predicate evaluates to true, the query result contains the aggregate row. Otherwise, the query result contains no rows. See HAVING Clause above. For example, SELECT COUNT(DISTINCT pno) number_parts, SUM(qty) total_parts FROM sp number_parts total_parts 2 1400 Subqueries are often Aggregate Queries. For example, parts with suppliers: SELECT * FROM p WHERE (SELECT COUNT(*) FROM sp WHERE sp.pno=p.pno) > 0 pno descr color P1 Widget Blue P2 Widget Red Parts with multiple suppliers: SELECT * FROM p WHERE (SELECT COUNT(DISTINCT sno) FROM sp WHERE sp.pno=p.pno) > 1 pno descr color P1 Widget Blue Union Queries The SQL UNION operator combines the results of two queries into a composite result. The component queries can be SELECT/FROM queries with optional WHERE/GROUP BY/HAVING clauses. The UNION operator has the following general format: query-1 UNION [ALL] query-2 query-1 and query-2 are full query specifications. The UNION operator creates a new query result that includes rows from each component query. By default, UNION eliminates duplicate rows in its composite results. The optional ALL specifier requests that duplicates be retained in the UNION result. The component queries of a Union Query can also be Union Queries themselves. Parentheses are used for grouping queries. The select lists from the component queries must be union-compatible. They must match in degree (number of columns). For Entry Level SQL92, the column descriptor (data type and precision, scale) for each corresponding column must match. The rules for Intermediate Level SQL92 are less restrictive. See Union-Compatible Queries. Union-Compatible Queries For Entry Level SQL92, each corresponding column of both queries must have the same column descriptor in order for two queries to be union-compatible. The rules are less restrictive for Intermediate Level SQL92. It supports automatic conversion within type categories. In general, the resulting data type will be the broader type. The corresponding columns need only be in the same data type category: Character (String) -- fixed/variable length Bit String -- fixed/variable length Exact Numeric (fixed point) -- integer/decimal Approximate Numeric (floating point) -- float/double Datetime -- sub-category must be the same, Date Time Timestamp Interval -- sub-category must be the same, Year-month Day-time UNION Examples SELECT * FROM sp UNION SELECT CAST(' ' AS VARCHAR(5)), pno, CAST(0 AS INT) FROM p WHERE pno NOT IN (SELECT pno FROM sp) sno pno qty S1 P1 NULL S2 P1 200 S3 P1 1000 S3 P2 200 P3 0
SQL-Transaction Statements SQL-Transaction Statements control transactions in database access. This subset of SQL is also called the Data Control Language for SQL (SQL DCL). There are 2 SQL-Transaction Statements: COMMIT Statement -- commit (make persistent) all changes for the current transaction ROLLBACK Statement -- roll back (rescind) all changes for the current transaction Transaction Overview A database transaction is a larger unit that frames multiple SQL statements. A transaction ensures that the action of the framed statements is atomic with respect to recovery. A SQL Modification Statement has limited effect. A given statement can only directly modify the contents of a single table (Referential Integrity effects may cause indirect modification of other tables.) The upshot is that operations which require modification of several tables must involve multiple modification statements. A classic example is a bank operation that transfers funds from one type of account to another, requiring updates to 2 tables. Transactions provide a way to group these multiple statements in one atomic unit. In SQL92, there is no BEGIN TRANSACTION statement. A transaction begins with the execution of a SQL-Data statement when there is no current transaction. All subsequent SQL-Data statements until COMMIT or ROLLBACK become part of the transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction. A subsequent SQL-Data statement starts a new transaction. In terms of direct effect on the database, it is the SQL Modification Statements that are the main consideration since they change data. The total set of changes to the database by the modification statements in a transaction are treated as an atomic unit through the actions of the transaction. The set of changes either: Is made fully persistent in the database through the action of the COMMIT Statement, or Has no persistent effect whatever on the database, through: the action of the ROLLBACK Statement, abnormal termination of the client requesting the transaction, or abnormal termination of the transaction by the DBMS. This may be an action by the system (deadlock resolution) or by an administrative agent, or it may be an abnormal termination of the DBMS itself. In the latter case, the DBMS must roll back any active transactions during recovery. The DBMS must ensure that the effect of a transaction is not partial. All changes in a transaction must be made persistent, or no changes from the transaction must be made persistent. Transaction Isolation In most cases, transactions are executed under a client connection to the DBMS. Multiple client connections can initiate transactions at the same time. This is known as concurrent transactions. In the relational model, each transaction is completely isolated from other active transactions. After initiation, a transaction can only see changes to the database made by transactions committed prior to starting the new transaction. Changes made by concurrent transactions are not seen by SQL DML query and modification statements. This is known as full isolation or Serializable transactions. SQL92 defines Serializable for transactions. However, fully serialized transactions can impact performance. For this reason, SQL92 allows additional isolation modes that reduce the isolation between concurrent transactions. SQL92 defines 3 other isolation modes, but support by existing DBMSs is often incomplete and doesn't always match the SQL92 modes. Check the documentation of your DBMS for more details. Transaction isolation controls the visibility of changes between transactions in different sessions (connections). It determines if queries in one session can see changes made by a transaction in another session. There are 4 levels of transaction isolation. The level providing the greatest isolation from other transactions is Serializable. At transaction isolation level Serializable, a transaction is fully isolated from changes made by other sessions. Queries issued under Serializable transactions cannot see any subsequent changes, committed or not, from other transactions. The effect is the same as if transactions were serial, that is, each transaction completing before another one is begun. At the opposite end of the spectrum is Read Uncommitted. It is the lowest level of isolation. With Read Uncommitted, a session can read (query) subsequent changes made by other sessions, either committed or uncommitted. Read uncommitted transactions have the following characteristics: Dirty Read -- a session can read rows changed by transactions in other sessions that have not been committed. If the other session then rolls back its transaction, subsequent reads of the same rows will find column values returned to previous values, deleted rows reappearing and rows inserted by the other transaction missing. Non-repeatable Read -- a session can read a row in a transaction. Another session then changes the row (UPDATE or DELETE) and commits its transaction. If the first session subsequently re-reads the row in the same transaction, it will see the change. Phantoms -- a session can read a set of rows in a transaction that satisfies a search condition (which might be all rows). Another session then generates a row (INSERT) that satisfies the search condition and commits its transaction. If the first session subsequently repeats the search in the same transaction, it will see the new row. The other transaction levels -- Read Committed, Repeatable Read and Serializable, will not read uncommitted changes. Dirty reads are not possible. The next level above Read Uncommitted is Read Committed, and the next above that is Repeatable Read. In Read Committed isolation level, Dirty Reads are not possible, but Non-repeatable Reads and Phantoms are possible. In Repeatable Read isolation level, Dirty Reads and Non-repeatable Reads are not possible but Phantoms are. In Serializable, Dirty Reads, Non-repeatable Reads, and Phantoms are not possible. The isolation provided by each transaction isolation level is summarized below:
Dirty Reads Non-repeatable Reads Phantoms Read Uncommitted Y Y Y Read Committed N Y Y Repeatable Read N N Y Serializable N N N Note: SQL92 defines the SET TRANSACTION statement to set the transaction isolation level for a session, but most DBMSs support a function/method in the Client API as an alternative. SQL-Schema Statements in Transactions The 3rd type of SQL Statements - SQL-Schema Statements, may participate in the transaction mechanism. SQL-Schema statements can either be: included in a transaction along with SQL-Data statements, required to be in separate transactions, or ignored by the transaction mechanism (can't be rolled back). SQL92 leaves the choice up to the individual DBMS. It is implementation defined behavior. COMMIT Statement The COMMIT Statement terminates the current transaction and makes all changes under the transaction persistent. It commits the changes to the database. The COMMIT statement has the following general format: COMMIT [WORK] WORK is an optional keyword that does not change the semantics of COMMIT. ROLLBACK Statement The ROLLBACK Statement terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. The ROLLBACK statement has the following general format: ROLLBACK [WORK] WORK is an optional keyword that does not change the semantics of ROLLBACK. Data Definition Language A Data Definition Language is a computer language for defining data. XML Schema is an example of a pure DDL (although only relevant in the context of XML). A subset of SQL's instructions form another DDL. For example in Oracle the DDL statements refer to CREATE, DROP, ALTER, etc.. These SQL statements define the structure of a database, including rows, columns, tables, indexes, and database specifics such as file locations. DDL SQL statements are more part of the DBMS and have large differences between the SQL variations. DDL SQL commands include the following: Create - To make a new database, table, index, or stored query. Drop - To destroy an existing database, table, index, or view. Alter - To modify an existing database object. DBCC (Database Console Commands) - Statements check the physical and logical consistency of a database.
Data Manipulation Language Data Manipulation Language (DML): is a family of computer languages used by computer programs or database users to retrieve, insert, delete and update data in a database. Currently, the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. Other forms of DML are those used by IMS/DL1, CODASYL databases (such as IDMS), and others. Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people, as well. Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are "select", "insert", "update", and "delete". This makes the nature of the language into a set of imperative statements (commands) to the database. Data manipulation languages tend to have many different "flavors" and capabilities between database vendors. There has been a standard established for SQL by ANSI, but vendors still "exceed" the standard and provide their own extensions. Data manipulation language is basically of two types: 1) Procedural DMLs 2) Declarative DMLs
Data Control Language A Data Control Language is a computer language for controlling access to data in a database. Examples of DCL commands are GRANT to allow specified users to perform specified tasks. DENY to disallow specified users from performing specified tasks. REVOKE to cancel previously granted or denied permissions. UPDATE to allow a user to update records READ disallows a user to edit the database, can only view the data DELETE allows a user to delete records in a database Data modeling In information system design, data modeling is the analysis and design of the information in the system, concentrating on the logical entities and the logical dependencies between these entities
Views A view is a derived table registered in the catalog. A view is defined using a SQL query. The view is dynamically derived, that is, its contents are materialized for each use. Views are added to the catalog with the CREATE VIEW Statement. Once defined in the catalog, a view can substitute for a table in SQL-Data statements. A view name can be used instead of a base table name in the FROM clause of a SELECT statement. Views can also be the subject of a modification statement with some restrictions. CREATE VIEW Statement The CREATE VIEW statement creates a new database view. A view is effectively a SQL query stored in the catalog. The CREATE VIEW has the following general format:
CREATE VIEW view-name [ ( column-list ) ] AS query-1
[ WITH [CASCADED|LOCAL] CHECK OPTION ]
view-name is the name for the new view. column-list is an optional list of names for the columns of the view, comma separated. query-1 is any SELECT statement without an ORDER BY clause. The optional WITH CHECK OPTION clause is a constraint on updatable views. column-list must have the same number of columns as the select list in query-1. If column-list is omitted, all items in the select list of query-1 must be named. In either case, duplicate column names are not allowed for a view. The optional WITH CHECK OPTION clause only applies to updatable views. It affects SQL INSERT and UPDATE statements. If WITH CHECK OPTION is specified, the WHERE predicate for query-1 must evaluate to true for the added row or the changed row. The CASCADED and LOCAL specifiers apply when the underlying table for query-1 is another view. CASCADED requests that WITH CHECK OPTION apply to all underlying views (to any level.) LOCAL requests that the current WITH CHECK OPTION apply only to this view. LOCAL is the default. CREATE VIEW Examples Parts with suppliers: CREATE VIEW supplied_parts AS
SELECT *
FROM p
WHERE pno IN (SELECT pno FROM sp)
WITH CHECK OPTION
CREATE TABLE Statement The CREATE TABLE Statement creates a new base table. It adds the table description to the catalog. A base table is a logical entity with persistence. The logical description of a base table consists of: Schema -- the logical database schema the table resides in Table Name -- a name unique among tables and views in the Schema Column List -- an ordered list of column declarations (name, data type) Constraints -- a list of constraints on the contents of the table The CREATE TABLE Statement has the following general format: CREATE TABLE table-name ({column-descr|constraint} [,{column-descr|constraint}]...) table-name is the new name for the table. column-descr is a column declaration. constraint is a table constraint. The column declaration can include optional column constraints. The declaration has the following general format: column-name data-type [column-constraints] column-name is the name of the column and must be unique among the columns of the table. data-type declares the type of the column. Data types are described below. column-constraints is an optional list of column constraints with no separators. Constraints Constraint specifications add additional restrictions on the contents of the table. They are automatically enforced by the DBMS. The column constraints are: NOT NULL -- specifies that the column can't be set to null. If this constraint is not specified, the column is nullable, that is, it can be set to null. Normally, primary key columns are declared as NOT NULL. PRIMARY KEY -- specifies that this column is the only column in the primary key. There can be only one primary key declaration in a CREATE TABLE. For primary keys with multiple columns, use the PRIMARY KEY table constraint. See Entity Integrity below for a detailed description of primary keys. UNIQUE -- specifies that this column has a unique value or null for all rows of the table. REFERENCES -- specifies that this column is the only column in a foreign key. For foreign keys with multiple columns, use the FOREIGN KEY table constraint. See Referential Integrity below for a detailed description of primary keys. CHECK -- specifies a user defined constraint on the table. See the table constraint - CHECK, below. The table constraints are: PRIMARY KEY -- specifies the set of columns that comprise the primary key. There can be only one primary key declaration in a CREATE TABLE Statement. See Entity Integrity below for a detailed description of primary keys. UNIQUE -- specifies that a set of columns have unique values (or nulls) for all rows in the table. The UNIQUE specifier is followed by a parenthesized list of column names, separated by commas. FOREIGN KEY -- specifies the set of columns in a foreign key. See Referential Integrity below for a detailed description of foreign keys. CHECK -- specifies a user defined constraint, known as a check condition. The CHECK specifier is followed by a predicate enclosed in parentheses. For Intermediate Level SQL92, the CHECK predicate can only reference columns from the current table row, with no subqueries. Many DBMSs support subqueries in the check predicate. The check predicate must evaluate to not False (that is, the result must be True or Unknown) before a modification or addition of a row takes place. The check is effectively made on the contents of the table after the modification. For INSERT Statements, the predicate is evaluated as if the INSERT row were added to the table. For UPDATE Statements, the predicate is evaluated as if the row were updated. For DELETE Statements, the predicate is evaluated as if the row were deleted (Note: A check predicate is only useful for DELETE if a self-referencing subquery is used.)
Entity Integrity As mentioned earlier, the relational model requires that each base table have a Primary Key. SQL92, on the other hand, allows a table to created without a primary key. The advice here is to create all tables with primary keys. A primary key is a constraint on the contents of a table. In relational terms, the primary key maintains Entity Integrity for the table. It constrains the table as follows, For a given row, the set of values for the primary key columns must be unique from all other rows in the table, No primary key column can contain a null, and A table can have only one primary key (set of primary key columns).
Referential Integrity Foreign keys provide relationships between tables in the database. In relational, a foreign key in a table is a set of columns that reference the primary key of another table. For each row in the referencing table, the foreign key must match an existing primary key in the referenced table. The enforcement of this constraint is known as Referential Integrity. Referential Integrity requires that: The columns of a foreign key must match in number and type the columns of the primary key in the referenced table. The values of the foreign key columns in each row of the referencing table must match the values of the corresponding primary key columns for a row in the referenced table. The one exception to the second restriction is when the foreign key columns for a row contain nulls. Since primary keys should not contain nulls, a foreign key with nulls cannot match any row in the referenced table. However, a row with a foreign key where any foreign key column contains null is allowed in the referencing table. No corresponding primary key value in the referenced table is required when any one (or more) of the foreign key columns is null. Other columns in the foreign key may be null or non-null. Such a foreign key is a null reference, because it does not reference any row in the referenced table. Like other constraints, the referential integrity constraint restricts the contents of the referencing table, but it also may in effect restrict the contents of the referenced table. When a row in a table is referenced (through its primary key) by a foreign key in a row in another table, operations that affect its primary key columns have side-effects and may restrict the operation. Changing the primary key of or deleting a row which has referencing foreign keys would violate the referential integrity constraints on the referencing table if allowed to proceed. This is handled in two ways, The referenced table is restricted from making the change (and violating referential integrity in the referencing table), or Rows in the referencing table are modified so the referential integrity constraint is maintained. These actions are controlled by the referential integrity effects declarations, called referential triggers by SQL92. The referential integrity effect actions defined for SQL are: NO ACTION -- the change to the referenced (primary key) table is not performed. This is the default. CASCADE -- the change to the referenced table is propagated to the referencing (foreign key) table. SET NULL -- the foreign key columns in the referencing table are set to null. Update and delete have separate action declarations. For CASCADE, update and delete also operate differently: For update (the primary key column values have been modified), the corresponding foreign key columns for referencing rows are set to the new values. For delete (the primary key row is deleted), the referencing rows are deleted.
I think you are crazy!!
