Database Management Systems
Contents
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.
2011 Winter Class
Programming Examples
- Lecture 1: It really easy that easy to do small things
Commandline Connected Example
Forms Disconnected Example
- Lecture 2: Look at the Objects we used in the Forms from Lecture 1.
Filled by TableAdapter contained in the TableAdapterManager
BindingSource as a service provider
- How are GUI elements bound then?
- How do the GUI elements change?
- Lecture 3: Detail Tables, Validation and other GUI Objects
- What you bind to makes all the difference.
- Responding to the Validating Event
For anything other than small, independent projects consider the Entity Framework with LINQ.
Lecture 4: /EntityFrameworkExample
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.
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.
And many more to follow I'm sure.
Project Ideas
See: Project Ideas
Chapter Hints
READ THIS BEFORE YOU DO YOUR HOMEWORK and it will help you!
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 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 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;