Final Words

Its been 3 solid days of grinding slog and I now have a working database based on my Logical ERD.

A lot of tricky coding to get some of the Transactions to produce the exact results I wanted, but coding, while challenging is also fun.

The Final Test

I have deleted my entire database from SQL Server, all that hard work disappearing at the click of a mouse is a scary experience.

But like a Phoenix arising from the ashes, copy and pasting the SQL code, with a lot of sweating and breath holding, it all works and my Database is back to life.

Now just hope it works the same on the tutors system.

 

The Final Class

What to hand in for Milestone 3: The final report containing MS 1, MS 2 and MS 3, which includes the transaction analysis and any discussions on indexing and any major problems and how they were handled as well as an analysis of the size of our database.

We also need to hand in a .sql text document with all of our SQL statements and queries and procedures. This will allow Todd to recreate our databases and run everything and see how it all works.

And so to the last class and we learnt how SQL server manages security. The differences between permissions and securables and another visit to Lynda.com.

Securables: the database objects that you can apply security on your SQL server, a hierarchy of Server securables, Database and Schema securables.

Principals: these are the entities to which to which security is applied. Users or applications which interact with the database. Also a hierarchy, Windows level, SQL server level and Database level.

And then onto an exercise, create a new user on my computer and then add the to SQL server. Unfortunately the exercise, only 1 year old, is not supported on Windows 10 edition on my computer, I can’t just create a new user like the exercise does. So scrap that exercise.

The rest of the class is so focused on assignments that no one else noticed.

Happy with the marks for the last milestone 49/50 so now onto the final step and i’m making a database.

 

12/06/2018

Files, space and storage. How the database is stored in SQL server.

All about the size of the datatype from 1 byte for a tinyint to 4 bytes for an int

Char and varchar are a little different, char takes up the amount of space you specify, so char(10) uses 10 bytes.

Varchar takes the amount of space of the actual number of characters in bytes + 2 bytes, for example a varchar(10) containing a single character “a”, would only use 3 bytes of memory, not 10.

Our database will be huge because each drone transmits 10 rows of data every 10 seconds.

And now on to the Assignment.

 

08/06/2018

Nice discussion this evening after class with Todd. About my progress through the course and the fact that I’m doing the Bachelors. He also told me not to get angry and that seemed like a good excuse to discuss NaYLER analysis and the hundreds of sentences we had to write for our assignment. Good news for next year, it will probably be dropped.

Class today was a couple of video presentations on Indexing, the main two types Clustered and Unclustered, the differences between them and when they are used.

The indexing speeds up a query on the database  by using a B-Tree structure to stone the index. Clustered index is faster to query but slow to update while unclustered is the opposite.

In SQL Server a clustered index is automatically created if a table has a Primary Key.

And now onto Milestone 3 of the assignment and starting to analyse transactions which helps choose unclustered indexes.

01/06/2018

An introduction to Milestone 3, Todd discussed briefly what is expected for milestone 3 in class today. Creating the database in SQL Server, populating the database with made up data and the running the SQL queries and procedures to get information out.

Definitely he most interesting part of the Assessment.

I tweaked my Logical model again today, the Subscriber – video feed relationship wasn’t correctly modeled and the foreign key was in the video feed instead, I need the video feed ID as an attribute, the foreign key, in the Subscriber table. The Super platinum needed to be modeled as a many to many relationship, so now we need a join table between them.

The same applies to the relationship between Contracs and Data and I had o remodel these relationships and also needed a join table.

Now I have to update all my tables and NaYLER analysis to include these changes.

Have I mentioned how pointless NaYLER seems, a rather boring, repetitive process producing little value for the time and effort it takes.

29/05/2018

Milestone 2 and NaYLER analysis, allows us a different view of our logical model. This technique was developed by a previous NMIT lecturer and presented at a conference in Dunedin as a tool to help students.

Tricky to comment on but using Duck Duck Go, there are very few search engine hits, suggesting that the technique didn’t become mainstream in the Database world.

Anyway, we had to do it for milestone 2 of our assignment so I spent about 10 hours generating the statements and examples. Now today the announcement is that we only need examples for the relationship statements, lots of wasted time and effort because i have example for each and every sentence.

Class today

Interesting discussion on Views on a database. Essentially creating virtual tables on a database from the existing tables.

Views can be used to provide users permissions to view only selected portions of the database, so useful from a security aspect.

Views can also be used to update tables in the database, but there are many restrictions on how the updates happen, so mot overall a useful technique.

Next lesson we’ll learn more about Permission. In the meantime, I think my normalisation of the Logical model might be OTT, so hopefully can check this with Todd.

25/05/2018

Assignment work today, I spent the morning in the Library with the Begg and Connolly textbook in front of me.

Normalisation is not always the easiest topic to explain. Its actually amazing how much we normalise our databases without actually thinking or realising we are doing in. The first and second normal forms almost happen naturally during the conceptual design phase.

I have discovered that an address in a person table fails normalisation. 2 or more people for example a family can have the same address, so a better model is to take the address out of the person table and make a new table for storing Addresses.

Taking this a step further, we also store other contact details about people, in this assignment I have 3 tables all storing the same information, phone numbers, email address and physical address. So I took this to the next level and now have a single table which stores all the contact information for anyone associated with the drone company, no more redundancy.

So now I have lots of tables, each storing less information.

Class today was great, we got to watch a video of an American database expert playing with database constraints, mostly primary and foreign keys, but also includes strange little things like CHECK, great for validating data entry and that wonderful expression NULL, not to be confused with 0, it actually indicates an absence of a value, an empty space.

18/05/2015

Class today, was a brief discussion of Milestone one, seems there is a lot of unhappiness with the marks, listening to various discussions. But some good news came from this discussion, we have an extra week to submit Milestone 2.

So this week end I’ll be pulling apart my Conceptual model, fixing the mistakes and correcting it according to the comments from Todd.

Interesting that the Data and Video feed from the Drone are not attributes of the Drone but are entities themselves, I knew that they would be normalized out to become entities but I did think that in the conceptual model they could remain as attributes.

Sub-queries

In class today, we discussed sub-queries. We can put select statements in parentheses and run them as a query inside another query.

We can run a sub-query in the SELECT , FROM or WHERE parts of a query (The outer query), in fact pretty much anywhere, including running functions and procedures on a database. Like a method in C#, we can run small ‘programmes’ on our database and a lot of select statements can be nested in IF statements. Something we are doing a lot of in our other database course.

Some rules for sub-queries:

• You must enclose a sub-query in parenthesis.
• A sub-query must include a SELECT clause and a FROM clause.
• A sub-query can include optional WHERE, GROUP BY, and HAVING clauses.
• A sub-query cannot include COMPUTE or FOR BROWSE clauses.
• You can include an ORDER BY clause only when a TOP clause is included.
• You can nest sub-queries up to 32 levels.

I can just imagine a query with 32 nested sub-queries!

11/05/2018

Group by

This allows us to select a “sub-set” of values from a column. These are grouped together almost like creating a smaller table from the base table.

We can then perform calculations on the sub-set.

Even more impressive is GROUP BY ROLLUP, this creates a group for each combination of column expressions.

example, GROUP BY ROLLUP (col1, col2, col3, col4)

creates the following results

• col1, col2, col3, col4
• col1, col2, col3, Null
• col1. col2, Null, Null
• col1, Null, Null, Null
• Null, Null, Null, Null —– this would be the same as a count(*)

nice example using country and state and the totals by state and by country.

And GROUP BY CUBE (), which displays results of every possible combination of the selected columns.

 

08/05/2018

The Assignment Milestone 1 is expected to be reviewed and marked by the end of the week.

Class today

Everybodies favourites, SQL Join statements. Last year in DAT501 we were introduced the inner and outer joins but never actually taught how to generate SQL statements to do a join, we had to kind of make it up and o it ourselves.

Its a tricky concept getting the tables to join on an ID value which is common to both tables, but I’m slowly getting it.

Inner and Left Outer and Right Outer are the commonest