DAT601 | Jesse Drummond | 2018

Today's lesson pertained to an explanation of the Milestone three steps to get everyone on the same page before Todd started into a talk about database file structures and the different structures they may take. The following discussion resulted in a lot of in-depth information regarding storage sizes that is ideally relevant to the estimation of database storage size task we have to complete within milestone 3. The rest of the class has been assigned to milestone 3 work.

Today's lesson pertains to more information regarding physical database design. An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned. In SQL Server, a clustered index determines the physical order of data in a table. There can be only one clustered index per table (the clustered index IS the table). Today we have been advised to bring headphones in order to use a lynda.com tutorial as part of our learning today. Before this Todd has shown us a video explaining the concept of b-trees which is the system that indexes use to map references to data rows within a database, indexes can be clustered or non-clustered layout. Non-Clustered Indexes A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one pla...

Today's lesson began with an introduction talk about the transactional analysis we will be doing for our Milestone 3 report, Todd emphasized that we can do this in crows foot notation unlike the example showed us which was in crows foot notation instead. Todd explained that we are interested in bytes, not bits. Understanding transactions are our primary topic, throughout understanding transactions we need to understand the expected frequency of a particular transaction, the relation sand attributes access and the types of access, time constraints imposed on the transaction such as within 1s. If a transaction fails no changes should be made to the db. Transaction maps are interesting and used to show the path our transactions take through our database. A tabular form of this transactional analysis can be made using the same data shown on the transaction map as well as some additional details to show the transaction in an in-depth sense. One of ...

Today's lesson not many people have shown up due to our Milestone 2 being due tonight, Todd has opted to instead to an introduction to milestone 3 instead of going over important concepts. Milestone 3 integrates our previous milestones into one master document that described the whole process from conceptual to physical. As a class we are looking at the required transactions for milestone 3, these are the update queries that our system must be capable of, each of these will be separate procedures that produce the required results. Today I have wrapped up my milestone two after much deliberation, I feel that it is not in a perfect state but none-the-less I have to move on to other assignments and the next milestone for this class, my general philosophy was to produce a flexible database that is capable of the functionality the company will require. With this mindset, I have added some extra tables & relationships that other students do not have, whether this is correct or ...

Week twelve notes have been released which are summed up as: Milestone Two feedback SQL server permissions and views Introduction to milestone Three, how dig is the database? In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. Views can also be thought of as virtual tables, they behave like tables but are derived from queries themselves, views are saved within databases and indexed. This allows them to be used much in the same way as regular tables. How do I create a view? CREATE   VIEW  view_name  AS SELECT  column1, column2, ... FROM  table_name WHERE  condition; Once a view is created using this code then it is stored in the database for reuse. Views may be updated to add additional columns as required. A VIEW is generally used: To present the information data from a diffe...

 Today's lesson theme pertains to referential integrity which Todd has in part assumed we already are familiar with from our previous level 5 DAT course although he has left resources within the course content for those who need a refresher on this content. CASCADE on delete is mentioned as a form of aiding in referential integrity if a primary key is set to cascade on delete it will remove all rows from other tables that also include this key as a primary key. Constraint checks are our next topic and these are used to constrain an attribute so that an entry into its field must be between or of a specific value or set of values. After viewing a very insightful video on constrains checks we have been assigned the rest of the class has been assigned to assignment work.