Differences between revisions 21 and 71 (spanning 50 versions)
Revision 21 as of 2010-12-31 20:14:36
Size: 2747
Editor: 71-88-174-166
Comment:
Revision 71 as of 2015-03-25 19:58:09
Size: 10875
Editor: scot
Comment:
Deletions are marked like this. Additions are marked like this.
Line 2: Line 2:
<<TableOfContents>>

<<ChildPages(on=not:edit|AttachFile|LocalSiteMap, more_link=More..., max_pages=25, title=<h3>%s Child Pages</h3>)>>
Line 4: Line 8:
== 2011 Winter Class ==

 * Cptr319_2011_WinterSchedule
 * [[DatabaseManagementSystems/Cptr319Schedule2011|Course Schedule 2011]]
 * [[DatabaseManagementSystems/Cptr319Syllabus|Syllabus 2011]]
Line 23: Line 26:
 * Lecture 4: /EntityFrameworkExample Updated with WPF: /EntityFrameworkWpfExample ([[http://db.cs.southern.edu/Videos/CPTR319/WPFEF/WPFEF.html|Video]])
Line 24: Line 28:
== Programming Resources ==  * [[/AssociationTablePattern|ORM's and Entity Framework]]

 * [[http://weblogs.asp.net/scottgu/archive/2011/05/05/ef-code-first-and-data-scaffolding-with-the-asp-net-mvc-3-tools-update.aspx|Another Cool MVC 3 ASP.NET tool that builds most of your code for you]]

 * [[SqlBackupAll|Scripting a complete Backup]]

== Visio and ER diagrams ==

 * [[http://dra.cs.southern.edu/classes/cptr319/CPTR319_VisioExample/CPTR319_VisioExample.html|Visio Lecture on the Student Database 2011]]
 * [[http://dra.cs.southern.edu/classes/cptr319/CPTR319_VisioExample2011/|ER Diagrams in Visio - Lecture 2011]]
   * [[attachment:ER_Visio_Lecture_Input.docx|Data to use in the ER Diagram (.docx)]]
   * [[attachment:ER_Visio_Lecture_Output.vsd|Completed Visio ER Diagram]]

== Programming & Design Resources ==
Line 27: Line 44:
 * [[http://www.fabforce.net/dbdesigner4/|DBDesigner]] is an awesome tool that went for pay by oracle now. Get the old one and don't pay for the ability to reverse engineer a diagram from a database. Works with Oracle, MSSQL and MySQL.
Line 35: Line 53:
 * [[http://www.linqpad.net/|Link Pad]] allows you to write scratch c# LINQ code and claims it can be used as a drop in replacement for SQL Server Management Studio
Line 38: Line 55:

== Project Ideas ==

See: [[DatabaseManagementSystems/Cptr319ProjectIdeas|Project Ideas]]

== Chapter Notes and Hints ==

These notes and hints serve to review you for exams and to help you with your homework. Occasionally you will find information here that is not in the book. You should READ THIS BEFORE YOU DO YOUR HOMEWORK!

=== Chapter 3 and 4 Hints ===

If you are having problems joining tables in the FROM and WHERE clauses, you are in the majority. There are two hints that I will give you to help you obtain the right perspective on joining tables:

'''Rule 1:''' Just because two attributes have the same name doesn't mean that you should join them on these attributes. STOP, THINK about the semantics (the meaning) of the attribute. Do they both refer to the same thing? If the answer is yes, then you should join on them. Using NATURAL JOIN will match strings, not semantics, so be careful. Also NATURAL JOIN "works" but not on most systems and even on Oracle it is not encouraged... so DON'T USE IT ON YOUR HOMEWORK. If you do I'll run it on Oracle. If it doesn't work, you will be docked points accordingly.

'''Rule 2:''' When you are looking at the attributes and relations, something is a foreign key only if it is '''the''' primary key in a '''different''' relation. Even if the foreign key is part (but not all) of the key in the relation it is in, it is a foreign key.

'''Rule 3:''' The book doesn't always give you all the attributes that make up the primary key. Look carefully at the values and make sure when you create tables in Chapter 4 that the attributes you select to be the primary key are sufficient to uniquely identify a each row.

=== Chapter 5: Advanced SQL ===

 * /ViewExample
 * /ConcatenateStringsExample

=== Chapter 6.1: Relational Algebra ===

'''Rule 1:''' You don't need to qualify the results of a natural join. The results of a natural join ALWAYS only have one copy of overlapping attributes.

'''Rule 2:''' Predicates, attribute lists and functions in <<latex($\sigma, \Pi, \mathcal{G}$)>> are always subscript.

'''Rule 3:''' Relations, whether expressions or names, NEVER appear as a superscript or subscript.

'''Rule 4:''' Input relations, whether expressions or name, always have parenthesis around them. E.g.

<<latex(\Large$_{sect\_id,course\_id,year,semester}\mathcal{G}_{count(ID)}(student \bowtie takes)$)>>

=== Chapter 7 ===

Answers provided by the book to 7.2 seem a bit rough to me. You will find an image that expands on the book's answer here: [[attachment:Ch7.16 Annotated Drawings.pdf]]

=== Chapter 8 ===

==== Normal Forms ====

{{{#!latex

\usepackage{amsmath}%
\setcounter{MaxMatrixCols}{30}%
\usepackage{amsfonts}%
\usepackage{amssymb}%
\usepackage{graphicx}
\usepackage{geometry}
\newtheorem{theorem}{Theorem}
\newtheorem{acknowledgement}[theorem]{Acknowledgement}
\newtheorem{algorithm}[theorem]{Algorithm}
\newtheorem{axiom}[theorem]{Axiom}
\newtheorem{case}[theorem]{Case}
\newtheorem{claim}[theorem]{Claim}
\newtheorem{conclusion}[theorem]{Conclusion}
\newtheorem{condition}[theorem]{Condition}
\newtheorem{conjecture}[theorem]{Conjecture}
\newtheorem{corollary}[theorem]{Corollary}
\newtheorem{criterion}[theorem]{Criterion}
\newtheorem{definition}[theorem]{Definition}
\newtheorem{example}[theorem]{Example}
\newtheorem{exercise}[theorem]{Exercise}
\newtheorem{lemma}[theorem]{Lemma}
\newtheorem{notation}[theorem]{Notation}
\newtheorem{problem}[theorem]{Problem}
\newtheorem{proposition}[theorem]{Proposition}
\newtheorem{remark}[theorem]{Remark}
\newtheorem{solution}[theorem]{Solution}
\newtheorem{summary}[theorem]{Summary}
\newenvironment{proof}[1][Proof]{\noindent\textbf{#1.} }{\ \rule{0.5em}{0.5em}}
\geometry{left=0.5in,right=0.5in,top=0.5in,bottom=0.5in}

%%end-prologue%%

The following definitions are found in "Database System Concepts" Sixth Edition by Silberschatz, Korth and Sudarshan

\begin{definition}[First Normal Form p. 328]
A relation $R$ is in first normal form if the domains of all attributes of $R$ are atomic.
\end{definition}

\begin{definition}[Second Normal Form p. 372]
A relation $R$ is in second normal form if each attribute $A$ in $R$ meets one of the following criteria:
\begin{itemize}
  \item It appears in a candidate key
  \item It is not partially dependent on a candidate key.
\end{itemize}
\end{definition}

\begin{definition}[Partial Dependency p. 372]
A functional dependency $\alpha \rightarrow \beta$ is called a partial dependency if there is a proper subset $\gamma$ of $\alpha$ such that $\gamma \rightarrow \beta$. We say that $\beta$ is partially dependent on $\alpha$.
\end{definition}

\begin{definition}[Third Normal Form p. 336]
A relation schema $R$ is in third normal form with respect to a set $F$ of functional dependencies if, for all functional dependencies in $F^+$ of the form $\alpha \rightarrow \beta$, where $\alpha \subseteq R$ and $\beta \subseteq R$, at least one of the following holds:
\begin{itemize}
  \item $\alpha \rightarrow \beta$ is a trivial functional dependency (that is, $\beta \subseteq \alpha$).
  \item $\alpha$ is a superkey for schema $R$.
  \item Each attribute $A$ in $\beta - \alpha$ is contained in a candidate key for $R$.
\end{itemize}
\end{definition}

\begin{definition}[Boyce Codd Normal Form (BCNF) p. 333]
A relation schema $R$ is in BCNF with respect to a set $F$ of functional dependencies if, for all functional dependencies in $F^+$ of the form $\alpha \rightarrow \beta$, where $\alpha \subseteq R$ and $\beta \subseteq R$, at least one of the following holds:
\begin{itemize}
  \item $\alpha \rightarrow \beta$ is a trivial functional dependency (that is, $\beta \subseteq \alpha$).
  \item $\alpha$ is a superkey for schema $R$.
\end{itemize}
\end{definition}

\begin{definition}[Fourth Normal Form pp. 358-359]
A relation schema $r(R)$ is in fourth normal form with respect to a set $D$ of functional and multivalued dependencies if, for all multivalued dependencies in $D^+$ of the form $\alpha \rightarrow \rightarrow \beta$, where $\alpha \subseteq R$ and $\beta \subseteq R$, at least one of the following holds:
\begin{itemize}
  \item $\alpha \rightarrow \rightarrow \beta$ is a trivial multivalued dependency.
  \item $\alpha$ is a superkey for schema $R$.
\end{itemize}
\end{definition}
}}}

=== Chapter 20 ===

Using this data: [[attachment:Aircraft Data.xlsx]]

Import the data into a database and try the following query.

Try the following query:

{{{
USE Aircraft

GO

SELECT [Eng.HP], [Description], AVG(Cruise) AS AvgCruise,
    AVG(TopSpd) AS AvgTopSpeed,
    AVG([Gross Wt.]) AS AvgWt,
    AVG(UsefulLoad) AS avgUsefulLoad,
    AVG(GPH) AS AvgFuelFlow
FROM AirCraftData
GROUP BY [Eng.HP], [Description]
WITH CUBE
ORDER BY AvgFuelFlow;
}}}

Database Management Systems

Database Management Systems Child Pages

This class is about Database Management Systems. As such we talk about creating, managing and programming SQL databases. When time permits, we also cover spatial/spatial temporal data and database systems. This site contains content developed by Dr. Anderson for his classes.

Programming Examples

Visio and ER diagrams

Programming & Design Resources

Learning to program something more complex than a one off programming assignments for students can be difficult. Where do the experts learn to program large database applications and what tools and methods do they use? This section is not an answer, but a suggestion on where to look for those answers. Someday, I hope to have a class that explores this in depth.

And many more to follow I'm sure.

Project Ideas

See: Project Ideas

Chapter Notes and Hints

These notes and hints serve to review you for exams and to help you with your homework. Occasionally you will find information here that is not in the book. You should READ THIS BEFORE YOU DO YOUR HOMEWORK!

Chapter 3 and 4 Hints

If you are having problems joining tables in the FROM and WHERE clauses, you are in the majority. There are two hints that I will give you to help you obtain the right perspective on joining tables:

Rule 1: Just because two attributes have the same name doesn't mean that you should join them on these attributes. STOP, THINK about the semantics (the meaning) of the attribute. Do they both refer to the same thing? If the answer is yes, then you should join on them. Using NATURAL JOIN will match strings, not semantics, so be careful. Also NATURAL JOIN "works" but not on most systems and even on Oracle it is not encouraged... so DON'T USE IT ON YOUR HOMEWORK. If you do I'll run it on Oracle. If it doesn't work, you will be docked points accordingly.

Rule 2: When you are looking at the attributes and relations, something is a foreign key only if it is the primary key in a different relation. Even if the foreign key is part (but not all) of the key in the relation it is in, it is a foreign key.

Rule 3: The book doesn't always give you all the attributes that make up the primary key. Look carefully at the values and make sure when you create tables in Chapter 4 that the attributes you select to be the primary key are sufficient to uniquely identify a each row.

Chapter 5: Advanced SQL

Chapter 6.1: Relational Algebra

Rule 1: You don't need to qualify the results of a natural join. The results of a natural join ALWAYS only have one copy of overlapping attributes.

Rule 2: Predicates, attribute lists and functions in <<latex($\sigma, \Pi, \mathcal{G}$)>> are always subscript.

Rule 3: Relations, whether expressions or names, NEVER appear as a superscript or subscript.

Rule 4: Input relations, whether expressions or name, always have parenthesis around them. E.g.

<<latex(\Large$_{sect\_id,course\_id,year,semester}\mathcal{G}_{count(ID)}(student \bowtie takes)$)>>

Chapter 7

Answers provided by the book to 7.2 seem a bit rough to me. You will find an image that expands on the book's answer here: Ch7.16 Annotated Drawings.pdf

Chapter 8

Normal Forms

\usepackage{amsmath}%
\setcounter{MaxMatrixCols}{30}%
\usepackage{amsfonts}%
\usepackage{amssymb}%
\usepackage{graphicx}
\usepackage{geometry}
\newtheorem{theorem}{Theorem}
\newtheorem{acknowledgement}[theorem]{Acknowledgement}
\newtheorem{algorithm}[theorem]{Algorithm}
\newtheorem{axiom}[theorem]{Axiom}
\newtheorem{case}[theorem]{Case}
\newtheorem{claim}[theorem]{Claim}
\newtheorem{conclusion}[theorem]{Conclusion}
\newtheorem{condition}[theorem]{Condition}
\newtheorem{conjecture}[theorem]{Conjecture}
\newtheorem{corollary}[theorem]{Corollary}
\newtheorem{criterion}[theorem]{Criterion}
\newtheorem{definition}[theorem]{Definition}
\newtheorem{example}[theorem]{Example}
\newtheorem{exercise}[theorem]{Exercise}
\newtheorem{lemma}[theorem]{Lemma}
\newtheorem{notation}[theorem]{Notation}
\newtheorem{problem}[theorem]{Problem}
\newtheorem{proposition}[theorem]{Proposition}
\newtheorem{remark}[theorem]{Remark}
\newtheorem{solution}[theorem]{Solution}
\newtheorem{summary}[theorem]{Summary}
\newenvironment{proof}[1][Proof]{\noindent\textbf{#1.} }{\ \rule{0.5em}{0.5em}}
\geometry{left=0.5in,right=0.5in,top=0.5in,bottom=0.5in}

%%end-prologue%%

The following definitions are found in "Database System Concepts" Sixth Edition by Silberschatz, Korth and Sudarshan

\begin{definition}[First Normal Form p. 328]
A relation $R$ is in first normal form if the domains of all attributes of $R$ are atomic.
\end{definition}

\begin{definition}[Second Normal Form p. 372]
A relation $R$ is in second normal form if each attribute $A$ in $R$ meets one of the following criteria:
\begin{itemize}
  \item It appears in a candidate key
  \item It is not partially dependent on a candidate key.
\end{itemize}
\end{definition}

\begin{definition}[Partial Dependency p. 372]
A functional dependency $\alpha \rightarrow \beta$ is called a partial dependency if there is a proper subset $\gamma$ of $\alpha$ such that $\gamma \rightarrow \beta$. We say that $\beta$ is partially dependent on $\alpha$. 
\end{definition}

\begin{definition}[Third Normal Form p. 336]
A relation schema $R$ is in third normal form with respect to a set $F$ of functional dependencies if, for all functional dependencies in $F^+$ of the form $\alpha \rightarrow \beta$, where $\alpha \subseteq R$ and $\beta \subseteq R$, at least one of the following holds:
\begin{itemize}
  \item $\alpha \rightarrow \beta$ is a trivial functional dependency (that is, $\beta \subseteq \alpha$).
  \item $\alpha$ is a superkey for schema $R$.
  \item Each attribute $A$ in $\beta - \alpha$ is contained in a candidate key for $R$.
\end{itemize}
\end{definition}

\begin{definition}[Boyce Codd Normal Form (BCNF) p. 333]
A relation schema $R$ is in BCNF with respect to a set $F$ of functional dependencies if, for all functional dependencies in $F^+$ of the form $\alpha \rightarrow \beta$, where $\alpha \subseteq R$ and $\beta \subseteq R$, at least one of the following holds:
\begin{itemize}
  \item $\alpha \rightarrow \beta$ is a trivial functional dependency (that is, $\beta \subseteq \alpha$).
  \item $\alpha$ is a superkey for schema $R$.
\end{itemize}
\end{definition}

\begin{definition}[Fourth Normal Form pp. 358-359]
A relation schema $r(R)$ is in fourth normal form with respect to a set $D$ of functional and multivalued dependencies if, for all multivalued dependencies in $D^+$ of the form $\alpha \rightarrow \rightarrow \beta$, where $\alpha \subseteq R$ and $\beta \subseteq R$, at least one of the following holds:
\begin{itemize}
  \item $\alpha \rightarrow \rightarrow \beta$ is a trivial multivalued dependency.
  \item $\alpha$ is a superkey for schema $R$. 
\end{itemize}
\end{definition}

Chapter 20

Using this data: Aircraft Data.xlsx

Import the data into a database and try the following query.

Try the following query:

USE Aircraft

GO

SELECT [Eng.HP], [Description], AVG(Cruise) AS AvgCruise, 
                                AVG(TopSpd) AS AvgTopSpeed, 
                                AVG([Gross Wt.]) AS AvgWt,
                                AVG(UsefulLoad) AS avgUsefulLoad,
                                AVG(GPH) AS AvgFuelFlow
FROM AirCraftData
GROUP BY [Eng.HP], [Description]
WITH CUBE 
ORDER BY AvgFuelFlow;

DatabaseManagementSystems (last edited 2024-02-25 16:33:21 by scot)