The difference between T-sql and P/l-sql, Study Guides, Projects, Research of Computer science

Download The difference between T-sql and P/l-sql and more Study Guides, Projects, Research Computer science in PDF only on Docsity!

Name: Lehlohonolo Maleka, 577 509

Module: Database Development 361

Due Date: 09 October 2023

Lecturer: Mr Desire Sundire

Table of Contents

• Question 1: Codd’s 12 Rules
• i) Codd’s Rule 2 Implementation in SQL Server:
• Question 2:...............................................................................................................................................
  • i) What is T-SQL
  • ii) What is PL/ SQL
  • iii) Advantages:
  • iv) Disadvantages:
• v) Key Differences Between T-SQL and PL/SQL
• Question 3:...............................................................................................................................................
  • a) Optimized data storage
  • b) Security and Authentication
  • i) Windows Authentication:
  • ii) SQL Server Authentication:
  • iii) Security:
  • c) Backup and Recovery
  • d) Performance Monitoring and Tuning
  • e) Cloud Support
• References................................................................................................................................................

Rule 8: Changes made on a physical level must not impact and/or require a change to be made in the application program or level. It must stay independent and not cause update anomalies. This rule ensures that the DBMS is able to retrieve and optimize data storage without affecting the application. Rule 9: Changes made on a logical level must not impact and/or require a change to be made in the application program or level. It must stay independent and not cause update anomalies. This rule allows modification to the data model (master mode) without affecting data that relies on it. Rule 10: Integrity constraints must be defined and separated from the application programs and levels. This ensures that changing constraints will not cause anomalies by affecting the applications. Rule 11: The user should not be aware about the database location i.e., whether the database is distributed in multiple locations or not. This rule enforces support for scalability and simplifies database management. Rule 12: If a system provides a low-level language, then there should be no way to subvert or bypass the filter and integrity rules of high-level language of the system. Hierarchy exists for a reason and this rule enforces security measures and prevents data corruption. i) Codd’s Rule 2 Implementation in SQL Server:

Question 2: i) What is T-SQL Transaction Structured Query Language is an extension of SQL within Microsoft Server (EDUCBA, 2023). T-SQL has the functionalities of SQL with additional functionalities and the statements in T-SQL are used to execute a transaction to the database. ii) What is PL/ SQL PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic (GeeksforGeeks, 2019). The main key difference between T-SQL and PL/SQL is that T-SQL is proprietary, and PL/SQL is an open format. While statements are executed procedurally (DATAQUEST, 2021). T SQL contains local variable to logically structured the data and execute a transaction to the database Below is a few advantages and disadvantages of using T-SQL over SQL: iii) Advantages:

• Less complex and easier to understand.
• It has its own local variables that are independent from SQL.
• It has various support functions for things like string processing and mathematics.
• It has a different approach to the DELETE and UPDATE statements. It allows a FROM clause to be added.
• It has a BULK INSERT statement that allows the import of a data file into a database table. iv) Disadvantages:
• You don’t always have full control over everything.
• When used by companies and professional outlets, the cost can be high.
• The interface is extremely complicated and difficult to use.
• Although T-SQL conforms to ANSI and ISO standards, some databases go for proprietary. extensions to standard SQL to exploit “vendor lock-in”.
• Less powerful than PL/SQL due to the fact that it’s less complex.

• Based on the above example 1 , both the above statements perform the same execution, however with T-SQL the ‘TOP’ keyword can be used to return the top ten rows in the age column. This is beneficial as it reduces the number of lines in your code.
• Since T-SQL offers additional functionalities than regular SQL. The ISNULL function will replace NULL VALUES from a specific column, the SELECT ISNULL(0, age) will return an age of 0 for any rows that contain a value of null in the age column, whereas the commands in PL/ SQL SELECT COALESCE(age,0) function can be used to replace NULL values with a default value
• Based on the above example 2, In T-SQL, you use the DECLARE keyword to declare a variable and then use SET or SELECT to initialize it.
• In PL/SQL, you declare variables within a DECLARE section, and you can use the := operator for initialization. In some cases, like when using a SELECT statement, you use the INTO clause to assign a value to the variable. Question 3: How SQL Server provides database user and programmers with: a) Optimized data storage
• SQL databases can have multiple data and log files. The files are allocated on a disk space. The disk space allocated to a data file is logically organised in pages which is the fundamental unit of data storage in SQL Server. When any data is inserted into a SQL Server database, it saves the data to a series of 8 KB pages inside the data file.
• If multiple data files exist within a filegroup, SQL Server allocates pages to all data files based on a round-robin mechanism (Singh, 2023). So, when a user and programmer inserts data into a table, SQL Server allocates pages first to data file 1, then allocates to data file 2. For programmers this is useful as it ensures that data is correctly stored and optimised for easy altering and modification, while for the user it ensures that the data is accessible. b) Security and Authentication
• SQL Server provides security and authentication features that ensure that only users with authorized access have access to a database. This is done to prevent unauthorized manipulation of data as well as to protect the confidentiality, integrity, and accessibility of the data. There are a number of ways in which authentication can be done in SQL Server, the most prominent two ways of authenticating is through: i) Windows Authentication:
• An integrated security mode which allows users to authenticate using their windows credentials. The authentication uses windows operating systems authentication mechanisms to ensure the data is highly secure when accessing the data. This type of authentication is used when users are already authenticated using their windows credentials.

ii) SQL Server Authentication:

• Users and developers can authenticate using a username and password stored within SQL server. This type of authentication is less secure if not well managed, however it is useful when users don’t have windows accounts. iii) Security:
• SQL Server provides users and programmers with role based-security access to database objects such as tables and views and users can be assigned permissions to access the database objects. In SQL Server security is enforced through the system administrator, which manages all aspects of a SQL server instance.
• SQL server also provides security support for objects and programmers can specify who has SELECT, INSERT, UPDATE, DELETE permissions for individual objects c) Backup and Recovery
• Backups allow you to restore lost data in SQL Server. In the event that an instance is having a problem, you can restore it to a previous state by using a backup to overwrite it (Google Cloud,

d) Performance Monitoring and Tuning

• SQL Server provides database users and programmers with a variety of performance monitoring and tuning, one of the most prominent features is the SQL Server Management Studio, which is the primary interface for managing SQL Server databases and provides tools for monitoring and analysing database activity. SSMS allows tools such as activity monitoring by allowing users and developers to view real-time performance metrics such as disk usage and active queries and sessions and further allows optimization of query performance over time. e) Cloud Support
• SQL Server Offers cloud support through cloud SQL which is a fully managed relational database service with the purpose of enhancing database management by freeing the user of the database and programmers from administration takes which can vastly reduce the time it would take to manage data (Google Cloud, 2023). The cloud support is designed to offer a range of services that reduces the effort of building and maintaining database storage. The services include backups, network connectivity, export and import, maintenance, and updates, monitoring, and logging.