Database and Calculations Schema (C# to SQL)

[FrankBerrocal]

Coding of all structures in C#. Later, focused on Database and Calculations Schema.

[FrankBerrocal]

Waiting for complete script to gain time.

[FrankBerrocal]

Basic core SQL script completed.

Script uploaded to Repo.

[FrankBerrocal]

SQL script is being added as document for the project, for backup purposes. I have tested ALTER elements in C#.

Instead of completing 100% of script, I will code what I have now and include all the technical requirements after creating the DB via C#

Merge completed.

https://github.com/FrankBerrocal/BigSnowMan/pull/39

[FrankBerrocal]

First element, Alter Database

` Console.Write("Alter Database"); sb.Clear(); sb.Append("USE TestDB; ");

                sb.Append("ALTER DATABASE CURRENT");
                sb.Append("     SET RECOVERY FULL, ");
                sb.Append("     ANSI_NULLS ON,  ");
                sb.Append("     ANSI_PADDING ON, ");
                sb.Append("     ANSI_WARNINGS ON, ");
                sb.Append("     ARITHABORT ON,  ");
                sb.Append("     CONCAT_NULL_YIELDS_NULL ON,  ");
                sb.Append("     QUOTED_IDENTIFIER ON,  ");
                sb.Append("     NUMERIC_ROUNDABORT OFF,  ");
                sb.Append("     PAGE_VERIFY CHECKSUM,   ");
                sb.Append("     ALLOW_SNAPSHOT_ISOLATION ON;  ");
                //sb.Append("GO ");  This is T-SQL especific.  Cannot be used in SQLClient. SqlException (0x80131904): Incorrect syntax near 'GO'.
                sql = sb.ToString();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine(rowsAffected + " Database has been Altered");
                }`

[FrankBerrocal]

Following the structure of AdventureWorks database, I am creating the Schemas along with the database. Further research to add specific options is needed.

[FrankBerrocal]

Creation of Database admin userCreate schema CalculationSelecting Database-1 Schema created Create table CostVariance under schema Calculation Selecting Database-1 Table created Create table Schedule Variance under schema Calculation Selecting Database-1 Table created Create table Variance At Completion under schema Calculation Selecting Database-1 Table created Create table Cost Performance Index under schema Calculation Selecting Database-1 Table created Create table Schedule Performance Index under schema Calculation Selecting Database-1 Table created Create table Estimate At Completion under schema Calculation Selecting Database-1 Table created Create table To Complete Performance Index under schema Calculation Selecting Database-1 Table created Create table Total Cost under schema Calculation Selecting Database-1 Table created

[FrankBerrocal]

The process of C# to SQL Translation is slow, and valuation of changes should be made on both c# code and SQL script (since they both will be documentation material to two different stakeholders).

[FrankBerrocal]

What should be the schema of higher level tables?

https://www.simplilearn.com/tutorials/sql-tutorial/schema-in-sql

What is Schema in SQL?

1. In a SQL database, a schema is a list of logical structures of data.
2. A database user owns the schema, which has the same name as the database manager.
3. As of SQL Server 2005, a schema is an individual entity (container of objects) distinct from the user who constructs the object. In other words, schemas are similar to separate namespaces or containers used to handle database files.
4. Schemas may be assigned security permissions, making them an effective method for distinguishing and defending database objects based on user access privileges.
5. It increases the database's stability for security-related management.

https://www.integrate.io/blog/complete-guide-to-database-schema-design-guide/

Type of schema organizations (design perspective).

1. Flat model: A “flat model” database schema organizes data in a single, two-dimensional array—think of a Microsoft Excel spreadsheet or a CSV file. This schema is best for simple tables and databases without complex relations between different entities.
2. Hierarchical model: Database schemas in a hierarchical model have a “tree-like” structure, with child nodes branching out from a root data node. This schema is ideal for storing nested data—for example, family trees or biological taxonomies.
3. Network model: The network model, like the hierarchical model, treats data as nodes connected to each other; however, it allows for more complex connections, such as many-to-many relationships and cycles. This schema can model the movement of goods and materials between locations, or the workflow required to accomplish a particular task.
4. Relational model: As discussed above, this model organizes data in a series of tables, rows, and columns, with relationships between different entities. We’ll mainly be working with the relational model in the remainder of this article.
5. Star schema: The star schema is an evolution of the relational model that organizes data into “facts” and “dimensions.” Fact data is numerical (e.g. the number of sales of a product), while dimensional data is descriptive (e.g. the product’s price, color, weight, etc.).
6. Snowflake schema: The snowflake schema is a further abstraction on top of the star schema. Fact tables point to dimensional tables, which can also have their own dimensional tables, expanding the descriptiveness possible within the database. (As you might have guessed, the “snowflake” schema is named after the intricate patterns of a snowflake, in which smaller structures radiate off of the central arms.)

[FrankBerrocal]

Hierarchical schema organization. Project will include higher level tables and those schemas will be assigned accordingly.

[FrankBerrocal]

All Calculation schema tables have been completed. I am working now on Project Schema: Project, Dashboard, project type, and knowledge area.

[FrankBerrocal]

Merge https://github.com/FrankBerrocal/BigSnowMan/pull/41

[FrankBerrocal]

Plan for today, I will be completing the Project, Cost, Schedule, and Scope Schema. This will be renamed as Database and Calculations Schema, in order to avoid documenting all the work in one single task. This should be mapped to user stories when completed.

[FrankBerrocal]

Database has been created and all Calculations tables create, same as the schemas. One User created for testing by I need to research more on the subject (general knowledge, not C# to SQL since that is working ok). I cannot associate the user to my schema.

[FrankBerrocal]

Learning

Estimated time: 16 hours. Learning how to send all elements correctly, also defining the database in SQL at the same time. This is a parallel task, whatever is decided to go on the database, is first defined, tested on the SQL script in SQL Server, then coded in C#, and then tested again to see the effects on the database. Container should be switched on and off since buffering affects memory, and multiple errors are received.