Blog 26

Today was the last DAT601 class and we worked on our projects. After spending many hours fighting my SQL Server Delete queries last night I got all my SQL coding completed.

In class today Todd covered what was required for the milestone three hand-in due on the 17th of June. Then he covered the theory of the course to date.

A key theory in the class was the idea of the transformation between the conceptual to logical model, and the logical to physical model.

MS3 needs to have:

  •  A single report holding all three milestone reports.
  • Tell Todd you want to have milestone 1 and 2 remarked.
  • Hand-in  zipped folder with Word report and all additional files.
  • All SQL in 1 .txt or SQL file- i.e. create and transactions in 1 file. Put SQL in word document report to describe it as well.
  • In MS3 describe why you are doing what you are doing don’t just mechanically do it.

 

MS3 needs:

  • Read physical design handout
  • Step 5.2  May need to use heap and indexing at the start to choose a file organization.
  • Don’t  do steps 5.4,  6.1, 6.2 but talk about each. Never do step 5.4 unless you have a really good reason to do it
  • Step 7 -Look at forum post for 8th of June

You can use each of the steps in physical design process as a heading in your report. Must also do the checklist:

  1. Build the database in SQL Server using DDL statements.  (Step 4.1 creating the database why did I do it? Because I want to test database in this situation)    
  2. Generate and load appropriate test data. There should be a minimum of 100 rows across all the tables and no table should contain less than 5 rows. Provide a list of data in your tables. Please provide the appropriate DML statements for this step.    (Use INSERT STATEMENTS talk about the INSERT why did I choose this data why is it like this)
  3. Write and run a set of SQL queries required to provide the information needs  for the project case study. These will be resolved during class discussions, but should be determined from the case study documentation. Provide the SQL queries and their results. Include a brief description of the purpose of each query.

OPERATIONS REQUIRED!! (Can treat each of these as a SQL Server procedure)

  1. Add code, (Multiple INSERTS required)
  2. Login, Logout,
  3. View running agents,
  4. Agents with their models
  5. Models by Developer
  6. List devices by developer,

CRUD on these:

  1. Models,
  2. Developer,
  3. Agents
  4. Devices

 

  1. Document any significant problem(s) you encountered and the approach you took to solving it/them. This should show you made a genuine effort to solve these problem(s) and the learning you achieved from that process. (And reasons why you’ve done things. Errors you have encountered).

 

 

 

 

 

 

Advertisements
Blog 26

Blog 25

Today in the class of the 7th of May we discussed GROUP BY HAVING.

GROUP BY clumps things together, and works on groups rather than the whole table. Then a function can be used to calculate values on the GROUP BY values.

e.g. MAX(copies_in_stock) max,

This is getting the maximum value of the copies_in_stock column.

A clear definition of HAVING and GROUP BY clauses is:

HAVING works on the result of GROUP BY. Filters result of GROUP BY if you’re only interested in certain results from GROUP BY. Filters results of GROUP BY. The GROUP BY query creates the columns for the GROUP BY  values.

WHERE happens on the whole table before you do the GROUP BY, so WHERE restrict the columns used  before the GROUP BY.

e.g.

SELECT poet, 
MAX(copies_in_stock) AS max, 
MIN(copies_in_stock) AS min, 
AVG(copies_in_stock) AS avg, 
SUM(copies_in_stock) AS sum 
FROM writer GROUP BY poet HAVING max > 5;

GROUP BY poet so we have all the records in two groups as all there is only two poets.  Then we use the HAVING clause to restrict the rows of the GROUP BY to poets having results greater than.

You can duplicate columns in the common function outputs of the SQL columns.

HAVING is a little bit sensitive on the things you can do HAVING on.

 

Class notes:

We used the example code:

CREATE TABLE [writer] (
[poet] varchar(50) default NULL,
[anthology] varchar(40) default NULL,
[copies_in_stock] tinyint
default NULL
);

INSERT INTO [writer] VALUES
(‘Mongane Wally Serote’,’Tstetlo’,3),
(‘Mongane Wally Serote’,
‘No Baby Must Weep’,8),
(‘Mongane Wally Serote’,
‘A Tough Tale’,2),
(‘Douglas Livingstone’,
‘The Skull in the Mud’,21),
(‘Douglas Livingstone’,
‘A Littoral Zone’,2);

XX SELECT poet,copies_in_stock FROM writer GROUP BY poet; XX

SELECT * FROM writer;

SELECT poet,
MAX(copies_in_stock) max,
MIN(copies_in_stock) min,
AVG(copies_in_stock) avg,
SUM(copies_in_stock) sum
FROM writer GROUP BY poet;

SELECT poet,
MAX(copies_in_stock) AS max,
MIN(copies_in_stock) AS min,
AVG(copies_in_stock) AS avg,
SUM(copies_in_stock) AS sum
FROM writer
GROUP BY poet
HAVING MAX(copies_in_stock) > 5;

 

Project work:

We may use GROUP BY and HAVING clauses in the Developer having devices query.

Becca asked the questions: What kinds of constraints should be use? Todd replied that we can use NOT NULL, ON UPDATE CASCADE, and ON DELETE..

Unique is a constraint that tells you that a value should or shouldn’t be stored.

We can either add the constraints before of after the DDL SQL.

Do we have to write triggers? No we don’t have to write triggers, and create audit tables. However we do need to discuss them.

A constraint is anything that makes data behave in anything but the default behavior. Constraints are also called semantic constraints, and are set by the administrator according to the business rules.

Tonight I want to implement the constraints into my DDL SQL, and then discuss the steps 4.1, 4.2 primary keys, foreign keys, alternate keys, base relations, domains, triggers (and audit tables), and transaction maps.

Blog 25

Blog 24

Today in the class of Friday the 3rd of June we discussed how information is stored in databases. Each table has one or more partitions which store rows rather than columns. Then under the partition we have can have either heaps, clustered indexes or non clustered index. The default behavior is 1 partition per table.

In a DBMS data is stored in blocks of the size 8Kb. The unit of storage is in rows not columns, the below quote explains how SQL Server stores data:

“A table is contained in one or more partitions and each partition contains data rows in either a heap or a clustered index structure. The pages of the heap or clustered index are managed in one or more allocation units, depending on the column types in the data rows.”(“Table and Index Organization,” n.d.).

Indexes are used by queries to find data quickly. SO indexes are effectively like contents of a report, that specify the sections of a book. SO if the reader wants to get to a specific part of the book then they can find the appropriate address in the contents and then go the appropriate section.

If a table has no index then a query will have to look through all data from start to end which is called a table scan, which is bad for performance. But with index the query can find the area of data with values matching the

To create index on table write:

CREATE INDEX (indexname) ix_tablename_columncreatingindexon

ON tablename (column ordering style)

e.g.

CREATE Index IX_tblEmployee_Salary

ON tblEmployee (SALARY ASC)

We can now seek indexes from index pick up the row address and go to that row address in the table rather than searching whole table. We can specify the type of index on a non-key attribute by writing either ‘clustered’ or ‘non-clustered’.

Creating a clustered index on non-key attributes

create  clustered index idx_Product_Name_Price

on dboProduct (ProductName, ASC, ProductPrice ASC);

drop index dbo.Product.idx_Product_Name_Price;

go

 

Creating a nonclustered index on non-key attributes

create nonclustered index idx_Vendor_City

on dbo.Vendor(VendorName SC, VendorCity ASC);

go

select VendorName,CityName

from dbo.Vendor

go

 

You don’t have to write the above code to organize the primary and unique key attributes as by default SQL Server when you make a primary key makes clustered index on primary key constraint, (the primary key must be indexed but you can make it unclustered) whilst unique constraint is unclustered.

The only way you get a heaped table is if it has no key (i.e. no primary key or unique attribute). Heap structure is the default structure of database, but if you use a primary or unique key constraint then either a clustered or unclustered index is made respectively. You can have tables without keys, and this would make a non-indexed heap.

You can only make 1 clustered index per table, you can make many nonclustered indexes per column. You can’t filter a clustered index (filter specific columns).

 

The partition can have clustered, un-clustered or heap. Clustered indexes are more user friendly to end user to select. But inefficient to the database administrator to update the tables. Clustered index-Only 1 per table. Data been organized. The primary key constraint causes a clustered index to a primary key in the table.

Un-clustered- Pointers to where the data is.But data is not organized, unlike clustered indexes.

 

Homework:

Explain-Explains SQL’s plan to perform a query.

 

Project:

MS3 requirements:

Must write about all steps not just perform them.

You may have a different user view between developer and administrator.

Step 7: Run your test database, containing test data. Put in at least 5 rows per

Build database

DML Statements-INSERT statements

Write SQL queries-  Todd specifically wants the following:

OPERATIONS REQUIRED!!

  1. Add code,
  2. login, logout,
  3. view running agents,
  4. Agents with their models
  5. Models by Developer
  6. list devices by developer,

CRUD on these:

  1. models,
  2. Developer,
  3. Agents
  4. Devices,

 

IN SQL Server

Every table has 1 or more partition

Under partition it points to nodes in tree

Thus  SQL Server use one of two

 

Heap-Stored in whatever order they arrive.

Can also have indexed views which s

 

To calculate the size of the database you use the system files. If you have clustering it can have certain effects on the system. So to find out more about the system go to the system view.

Bibliography:

Table and Index Organization. (n.d.). Retrieved June 3, 2016, from https://msdn.microsoft.com/en-us/library/ms189051(v=sql.105).aspx

 

 

Blog 24

Blog 23

Today in the class of Tuesday the 31st of May we covered what should be in the final milestone and did some more work with SQL Server.

In the final Milestone three hand in we should combine milestone one, two, and three into a single report due on the 17th of June, so the milestone 3 report will effectively be the final assignment.

Class Notes:

Milestone 3 should cover, everything except step 5.4 in the below screenshot:

database

 

(Connolly & Begg, 2010).

In milestone three we investigate what operations there are that work on the data in the database, and we write queries for that data. These queries are contained in transactions. Todd recommended creating the following SQL transactions:

Logging in, registering device (and removing device), registering model, running an agent, deleting model, make code, editing of code, deleting code.

We then perform analysis of transactions. To do this we take current model and take look at how the system works. We count through system to see how big it is, and what attributes are hit the most. For attributes that a hit a lot we use secondary indexes. Secondary indexes are indexes beyond primary key used to improve access to the system.

So your transaction analysis could say: “For each of my transactions the attribute X  was not hit enough to merit the user of indexes”

We also talk about file organizations which is how data is stored in the physical platform, in which we’ll talk about on Friday. There is a overhead for specific file types which costs memory. File organization are thought of as blocks, every chunk of data stored in the computer is stored in blocks of 8Gb.

Today we looked at Security mechanisms using GRANT PERMISSIONS which allow you to grant permissions to a user account to INSERT, DELETE, SELECT, and UPDATE specific tables in the database.

Each time you create a GRANT permission for a user it  creates an entry in system table. Admins can access systems tables.

As part of today’s class we worked in pairs to write GRANT permissions in a Google Doc. Each DBMS (Database Management System) has different syntax for their GRANT statement. The syntax for a GRANT privilege for SQLServer from the TechOnTheNet website is below:

GRANT privileges ON object TO user;

As well as granting permissions you can also REVOKE permissions

In the google doc we wrote select, insert, update, delete, references, alter and explained GRANT ALL permissions. This is a link to the Google Doc that Amber and I worked on:

https://docs.google.com/document/d/18wW-d5hzkTU7Fq9prgleF0jPEMFawZc7Vz9EMFfp6ds/edit?usp=sharing

Starting the second SQL exercise of the lesson I learned:

We write:

CREATE PROCEDURE procedure-name          parameter-name        type

e.g.

CREATE PROCEDURE pr_Names @VarPrice money

The the procedure is started and finished in the following way:

BEGIN

AS

 

END

GO

In procedures we use @ as the prefix for a parameter rather than the ‘pr’ we use in MySQL.

We don’t use call we use

EXECUTE parametername parametervalue;

GO

e.g.

EXECUTE Login Alex;

GO

Project work:

I need to do lesson 1 and lesson 2 of the SQL Server tutorial before Fridays lesson.

I also need to finish writing up the DDL SQL for my tables, and then a UPDATE, SELECT, DELETE and INSERT into each of my tables, using 100 rows in test data in the overall database.

 Bibliography:

Connolly, T. M., & Begg, C. E. (2010). Database systems: a practical approach to design, implementation, and management (5th ed). Boston ; London: Addison-Wesley.

Blog 23

Blog 22

Today Friday the 28th of May because I had a crook stomach I did not feel up to attending class and so I stayed at home and worked on finishing off my Milestone two reports for my DAT601 and DAT602.

I wrote to Todd that I wasn’t able to attend today’s class because he has said previously that it is impolite to just not attend you need to write to him and give a reason why. I did so and he recommended that I read about transaction analysis which was the topic of the class.

I did have a read online about the transaction analysis.

I had stress uploading the Milestone two for DAT602 because my internet connection took longer to upload it to Google Drive than the DAT601 milestone one, therefore by the time it had uploaded to Google Drive, I had got the shareable link, and then pasted that into a text file and submitted the text file it was 12.01 and so my DAT602 milestone one report was submitted 6 minutes and 4 seconds late.

I was annoyed with myself for making this mistake of not realizing how long it would take to upload it to Google Drive but it was certainly not a lack of effort on my part, I did my very best.

I will now start working on the Milestone Three of the DAT601.

 

 

 

Blog 22

Blog 21

Today in the class of Tuesday the 24th of May we went over JOIN’s in SQL Server. JOIN’s in all SQL language versions are fairly similar.

Class Notes:

Todd suggested we used the MSDN Microsoft website for learning about SQL.

There are two types of joins: inner and outer join. Cross product join is a third type of join which we covered last week, it is where we selected project from multiple tables and then restrict using the WHERE clause.

INNER join based on things being the same across 2 tables. Where values from 2 columns are compared using comparison operator. Some systems only let you compare on keys (foreign and primary).

JOINs are specified either in WHERE clause (making a natural join), or JOIN in FROM clause. It is not necessarily faster to use JOIN in FROM clause it depends on the DBMS.

Outer joins create blanks in rows where there is no joining column.

In SQL server you can specify the schema (database) and then the table in that schema. You can set a column in a table in the schema to a variable using the AS clause. You use these variables because it makes table names shorter, and you can make the same table name with different variable.

FROM HumanResources.Employee AS e

INNER JOIN Person.Person AS p

ON e.BusinessEntityID=p.BusinessEntityID

ORDER BY p.LastName;

The column on which we are joining is BusinessEntityID column, in the humanresources and person tables.

In the ON clause you don’t just have to use the ‘=’  clause you can use AND and < or > and the not equal to ‘<>’  or NOT p1.List=p2.List.

Using INNER join you can build recursive relationships because you can store the same table in the same schema in two different variables. In other words making two copies of the same table.  p1 and p2 are copies of the same table, you cannot join on the same table unless you use the p1 and p2.

Distinct used in the SELECT query means if there is more copies of the same row in two tables it just give you one.

 

Therefore in summary INNER JOIN:

The word INNER is redundant it will run INNER JOIN even without the word INNER.

It is a equi-join; it is seeing 2 columns are same across 2 tables are the same to make a single column.

Where you are doing a comparison in the same table you must use the variables p1 and p2 to represent tables, otherwise it would throw an error. 2 copies of the same table.

However when comparing different tables you don’t have to use the variables. But many database developers do use variables out of habit.

OUTER JOIN

Todd used the example of Students table with the columns StudentID, Name, and Address, and 5 tuples.

With the table Course CourseID and Name and a join table CourseStudent with the attribute CourseID, StudentID, and Enrolement.

If we want to know list of all students on a course we would write:

SELECT cs.CourseID, s.Name

FROM CourseStudent AS cs

JOIN Student AS s 

ON cs.StudentID=s.StudentID (what are you joining on, this lines joins on when they are both equal in both tables)

ORDER BY cs.CourseID; (Order the results by)

This is a equi-join; only produces rows where the values match.

RIGHT OUTER JOIN:

But I want to know all students on a course and students not on a course, you use right outer join. The outer join produces all the values it can find, and where the value does not exist a NULL value is inserted. We do a right outer join to get values from the right hand side, and values that do not match on the left hand side have a NULL values entered.   :

SELECT cs.CourseID, s.Name

FROM CourseStudent AS cs

RIGHT JOIN Student AS s  (JOIN showing all values on right hand side)

ON cs.StudentID=s.StudentID (what are you joining on, this lines joins on when they are both equal in both tables)

ORDER BY cs.CourseID; (Order the results by)

You take every row on the right hand side and then if there is no value for that value on left hand side then write NULL.

RIGHT OUTER JOIN:

But in a LEFT JOIN we would get all the values from the left hand table and then where the equivalent value does not exist on the right hand table then we display a NULL value:

SELECT cs.CourseID, s.Name

FROM CourseStudent AS cs

LEFT JOIN Student AS s  (JOIN showing all values on left hand side)

ON cs.StudentID=s.StudentID (what are you joining on, this lines joins on when they are both equal in both tables)

ORDER BY cs.CourseID; (Order the results by)

Therefore to clarify the left join always produce something from left. The right join gives us something from the right. And in both cases where their is no associated value we write a NULL value.

Self join:

A join between two copies of the same table.

 

Then we did an exercise. I wrote the following code:
DROP TABLE Employee;
DROP TABLE Departments;

create table Employee(
id int identity(1,1) primary key,
Username varchar(50),
Firstname varchar (50),
LastName varchar (50),
DepartID int
);

create table Departments (
id int identity (1,1) primary key,
Departmentname varchar (50)
)

insert into Employee (Username, Firstname, Lastname, DepartID) VALUES (‘AlexBuckley’, ‘Alex’, ‘Buckley’, 1),
(‘AmberRielly’, ‘Amber’, ‘Rielly’, 2), (‘JonathanGrowcott’, ‘Jonathan’, ‘Growcott’, 1),
(‘DavidHarrison’, ‘David’, ‘Harrison’, 2), (‘ToddCochrane’, ‘Todd’, ‘Cochrane’, 0);

insert into Departments (Departmentname) VALUES (‘IT Department’), (‘Finance department’);

SELECT * FROM Employee e1 JOIN Departments e2 ON e1.DepartID=e2.id

SELECT * FROM  Employee e1 LEFT OUTER JOIN Departments e2 ON e1.DepartID=e2.id

SELECT  * FROM Employee e1 RIGHT OUTER JOIN  Departments e2 ON e1.DepartID=e2.id

SELECT * FROM Employee e1 FULL OUTER JOIN Departments e2 On e1.DepartID=e2.id

SELECT * FROM  Employee cross join Departments e2

SELECT  * FROM Employee, Departments e2

SELECT e1.Username, e1.Firstname, e1.Lastname from Employee e1 inner join Employee e2 on e1.id=e2.DepartID

create table employees (id int primary key, empName VARCHAR(50), managerID INT);

insert into employees (id, empName, managerID) VALUES (1, ‘Nirav’, 1), (2, ‘Rajan’, 1),
(3, ‘Hardik’, 2), (4, ‘Shweta’, 3), (5, ‘Kinjal’, 4), (6, ‘Shaily’, 5);

select e1.empName as ManagerName, e2. empName as EmpName from employees e1 inner join employees e2 on e1.id=e2.managerid

Project work:

I have now finished NaLER analysis with Becca and have finished writing up the rationale for my MS2. I checked through my MS2 and updated it making the logical model match the NaLER suggestions from step 2, as well as making sure the rationale I wrote several weeks ago had the local WiFi network and CodeType

Blog 21

Blog 20

Today Friday the 19th of May because class was optional I stayed at home and worked on finishing writing up the rationale for my DAT601 Milestone 1. Over the last few days I have worked my way through performing NaLER analysis on Becca’s logical entity relationship diagram which has a long time because she has made a very detailed logical model.

After sending my logical ER diagram that was in 3NF to Becca, I noticed that I had my optionality on the link lines between developer to Blinkup app and Model and the link between model and IMP OS depicted around the wrong way. In other way I had developer must utilize the blinkup app, but blinkup app doesn’t have to be utilized by the developer. This is of course wrong so I fixed the optionality of these links and sent the corrected logical entity relationship diagram to Becca.

I am unsure about some of Becca’s attributes for step 4 of NaLER where you have to insert example data into the NalER sentences from step 3.2 and step 3.3. So Becca and I have arranged to meet up to clarify the confusion.

 

Blog 20