Database Review

Document Sample
Database Review Powered By Docstoc
					Database Review

   Terminology
    Concepts
   SQL syntax
                    Analysis Terms
Term/Concept        Definition or Example
Relational Model    ?
Relation Instance   ?
Relation Schema     ?
Database Schema ?
Type Constraint     ?
Data Atomiciy       ?
                    Analysis Terms
Term/Concept        Definition or Example
Relational Model    Uses a n-ary relation between attribute sets to
                    model entity set.
Relation Instance   A table with a column for each attribute which is
                    atomic; each row represents an entity.
Relation Schema     A relation schema is a relation name (e.g. student,
                    customer, etc.) followed by a parenthesized list of
                    attribute names with associated domains, followed
                    by constraints.
Database Schema Collection of relation schemas.
Type Constraint     Value in column must be of corresponding data
                    type
Data Atomiciy       Single value in each column
                 Analysis Terms
Term/Concept     Definition or Example
Key              ?
Key Constraint   ?
Foreign Key      ?
Foreign Key      ?
Constraint
Candidate Key    ?
Superkey         ?
Functional       ?
Dependency
Determinant      ?
                 Analysis Terms
Term/Concept     Definition or Example
Key              A set of attributes which uniquely identifies an
                 entity (row) of a relation (table)
Key Constraint   Any relation or table has at most one row for some
                 combination of values of key attributes
Foreign Key      An attribute of a relation which serves as a primary
                 key of another relation
Foreign Key      There is a tuple or row of the primary key table with
Constraint       same value as value of foreign key (referential
                 integrity
Candidate Key    A minimal set of attributes which is a key
Superkey         Any key including set of all attributes of relation
Functional       A set of attributes X which uniquely determines a
Dependency       set Y of attributes : X  Y
Determinant      Left side of a functional dependency.
                  Analysis Terms
Term/Concept      Definition or Example
Anomaly           ?
Attribute         ?
Binary relation   ?
Cardinality       ?
Constraint
Composite Key     ?
Relational        ?
database
                  Analysis Terms
Term/Concept      Definition or Example
Anomaly           An error or inconsistency due to redundant data
                  resulting from insertion, update or deletion
Attribute         A property possessed by every entity in set
Binary relation   A relation between two entity sets.
Cardinality       A bound on the number of times an instance of an
Constraint        entity can be related to an instance of another
                  entity
Composite Key     A key consisting of more than one attribute.
Relational        A database consisting of relations (tables).
database
                  Analysis Terms
Term/Concept      Definition or Example
1st Normal Form   ?
Partial           ?
Dependency
2nd Normal Form   ?
Transitive        ?
Dependency
3rd Normal Form   ?
Boyce Codd        ?
Normal Form
                  Analysis Terms
Term/Concept      Definition or Example
1st Normal Form   Every attribute is atomic.
Partial           A functional dependency in which non-key
Dependency        attributes are functionally dependent on part but
                  not all of the primary key attributes.
2nd Normal Form   No partial dependencies
Transitive        A functional dependency between two non-key
Dependency        attributes or attribute sets.
3rd Normal Form   No transitive dependencies,
Boyce Codd        A relation in which every determinant is a
Normal Form       candidate key.
           SQL Syntax

• Create Table
• Insert
• Select
• Update
                Basic SQL Syntax
• Create Table
Create <Table> ( <List of Attribute-DataType pairs> <List of Constraints>)

• Insert
Insert Into <Table> (<Attributes List>) Values(<Value List>)

• Select
Select <Attributes List> From <Table Expression> Where <Condition>

• Update
Update <Table> Set <Attribute = New_Value List> Where <Condition>
                         SQL Statements
SQL       Syntax
Create    Create <Table> ( <List of Attribute-DataType pairs> <List of Constraints>)

Example   Create Table Category( Name Text, ID Number, Primary Key(ID))
Insert    Insert Into <Table> (<Attributes List>) Values(<Value List>)
Example   Insert Into Category (Name,ID) Values("Kitchen",1)
Select    Select <Attributes List> From <Table Expression> Where <Condition>
Example   Select Name From Category
Update    Update <Table> Set <Attribute = New_Value List> Where <Condition>
Example   UPDATE Furniture SET Furniture.Price = 20
          WHERE Furniture.ID=2




Create                Insert               Select                Update
                                 SQL
Term/Concept   Definition or Example

Create Table   Create <Table> ( <List of Attribute-DataType pairs> <List of
               Constraints>)
Example        Statement to create a transaction table as shown below.
                                 SQL
Term/Concept   Definition or Example

Create Table   Create <Table> ( <List of Attribute-DataType pairs> <List of
               Constraints>)
Example        Create Table Transaction(XDate date,
               Amount Currency,Payee Number,Comment text,
               ID number, Primary Key( ID))
Table
                                     SQL
Term/Concept            Definition or Example
Select                  Select <Attribute-List> From
                         <Table-Expression> Where <Condition>
<Attribute-List>        * refers to all attributes or columns
<Table-Expression>      RentalProperty is table
<Condition>
                         ID = PropertyID
Example                 Statement to select a rental property where ID is given
Embedded SQL            ID.
in as value of string
variable in front end
                                     SQL
Term/Concept            Definition or Example
Select                  Select <Attribute-List> From
                         <Table-Expression> Where <Condition>
<Attribute-List>        * refers to all attributes or columns
<Table-Expression>      RentalProperty is table
<Condition>
                         ID = PropertyID
Example                 Dim ds As DataSet
Embedded SQL            sql = "Select * from RentalProperty Where ID=" & Id
in as value of string   ds = ExecuteSql(sql)
variable in front end
                     SQL – Insert
Term/Concept   Definition or Example
Insert         Insert Into <Table Name> ( <List of Attributes>)
                                        Values ( <List of Values> )
Example
               Write statement to insert a transaction
               with the values shown below.
Table
                     SQL – Insert
Term/Concept   Definition or Example
Insert         Insert Into <Table Name> ( <List of Attributes>)
                                        Values ( <List of Values> )
Example
               INSERT INTO [Transaction] Values
               (#3/1/2009#,100,1,'',1)
Table
                     SQL – Update
Term/Concept   Definition or Example
Update         Update <Table Name> Set <List of Attribute = Value pairs>
Example        Write an Update statement to change the amount ot the
               given transaction to 150.

Table
                     SQL – Update
Term/Concept   Definition or Example
Update         Update <Table Name> Set <List of Attribute = Value pairs>
Example        UPDATE [Transaction] SET [Transaction].Amount = 150;
Table
                  SQL – Update
Term/Concept   Definition or Example
Select
Example
                        SQL – Select
Select
Example   Write a Select Query which select recipes from the joins of
          •Category
          •Recipe
          •RecipeCategories
              –    WHERE RecipeCategories.CategoryID = " & catID
                                        SQL – Select
Select    Public Function FindRecipesByCategory(ByVal catID As Integer) As List(Of Recipe)
              Dim sql As String
              Dim ds As DataSet, dr As DataRow
             sql = "SELECT Recipe.*" & _
              " FROM Category INNER JOIN (Recipe INNER JOIN RecipeCategories
          ON Recipe.ID = RecipeCategories.RecipeID) ON Category.ID =
          RecipeCategories.CategoryID " & _
Example       " WHERE RecipeCategories.CategoryID = " & catID
              ds = ExecuteSql(sql)
             Dim recipes As New List(Of Recipe)
             For Each dr In ds.Tables(0).Rows
               Dim c As New Recipe(dr)
               recipes.Add(c)
             Next
             Return recipes
           End Function
                                        SQL – Select
Public Function FindIngredientsForRecipe(ByVal rid As Integer) As List(Of Ingredient)
     Dim sql As String
     Dim ds As DataSet, dr As DataRow
   sql = "Select ingredient.* from " & _
    " (Recipe INNER JOIN RecipeIngredients ON Recipe.ID =
RecipeIngredients.RecipeID) INNER JOIN Ingredient ON
RecipeIngredients.IngredientID = Ingredient.ID " & _
    " WHERE Recipe.ID = " & rid
    ds = ExecuteSql(sql)
    Dim ingredients As New List(Of Ingredient)
    For Each dr In ds.Tables(0).Rows
      Dim c As New Ingredient(dr)
      ingredients.Add(c)
    Next
    Return ingredients
  End Function

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:10/21/2011
language:English
pages:24