Add Extended Properties to a Table and Column
We will add a few extended properties as follows.
/* The following extended properties already exist in the AdventureWorks database. There is no need to run the script against the database in order for the remaining samples to work. */ USE [AdventureWorks] GO --Script to add an Extended Property to the Table EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Street address information for customers, employees, and vendors.' , @level0type=N'SCHEMA', @level0name=N'Person', --Schema Name @level1type=N'TABLE', @level1name=N'Address' --Table Name GO --Script to add an Extended Property to a column EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'First street address line.' , @level0type=N'SCHEMA', @level0name=N'Person', --Schema Name @level1type=N'TABLE', @level1name=N'Address',--Table Name @level2type=N'COLUMN', @level2name=N'AddressLine1'--Column Name GO
Run the following script to generate the data dictionary and save the results to a file with an '.htm' extension.
Set nocount on DECLARE @TableName nvarchar(35) DECLARE Tbls CURSOR FOR Select distinct Table_name FROM INFORMATION_SCHEMA.COLUMNS --put any exclusions here --where table_name not like '%old' order by Table_name OPEN Tbls PRINT '<HTML><body>' FETCH NEXT FROM Tbls INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT '<br>' PRINT '<table border="1">' Print '<B>' + @TableName + '</B>' PRINT '<br>' --Get the Description of the table --Characters 1-250 Select substring(cast(Value as varchar(1000)),1,250) FROM sys.extended_properties A WHERE A.major_id = OBJECT_ID(@TableName) and name = 'MS_Description' and minor_id = 0 --Characters 251-500 Select substring(cast(Value as varchar(1000)),251, 250) FROM sys.extended_properties A WHERE A.major_id = OBJECT_ID(@TableName) and name = 'MS_Description' and minor_id = 0 PRINT '<tr>' --Set up the Column Headers for the Table PRINT '<td><b>Column Name</b></td>' PRINT '<td><b>Description</b></td>' PRINT '<td><b>InPrimaryKey</b></td>' PRINT '<td><b>IsForeignKey</b></td>' PRINT '<td><b>DataType</b></td>' PRINT '<td><b>Length</b></td>' PRINT '<td><b>Numeric Precision</b></td>' PRINT '<td><b>Numeric Scale</b></td>' PRINT '<td><b>Nullable</b></td>' PRINT '<td><b>Computed</b></td>' PRINT '<td><b>Identity</b></td>' PRINT '<td><b>Default Value</b></td>' --Get the Table Data SELECT '</tr>', '<tr>', '<td>' + CAST(clmns.name AS VARCHAR(35)) + '</td>', '<td>' + substring(ISNULL(CAST(exprop.value AS VARCHAR(255)),''),1,250), substring(ISNULL(CAST(exprop.value AS VARCHAR(500)),''),251,250) + '</td>', '<td>' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '</td>', '<td>' + CAST(ISNULL( (SELECT TOP 1 1 FROM sys.foreign_key_columns AS fkclmn WHERE fkclmn.parent_column_id = clmns.column_id AND fkclmn.parent_object_id = clmns.object_id ), 0) AS VARCHAR(20)) + '</td>', '<td>' + CAST(udt.name AS CHAR(15)) + '</td>' , '<td>' + CAST(CAST(CASE WHEN typ.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS INT) AS VARCHAR(20)) + '</td>', '<td>' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '</td>', '<td>' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '</td>', '<td>' + CAST(clmns.is_nullable AS VARCHAR(20)) + '</td>' , '<td>' + CAST(clmns.is_computed AS VARCHAR(20)) + '</td>' , '<td>' + CAST(clmns.is_identity AS VARCHAR(20)) + '</td>' , '<td>' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + '</td>' FROM sys.tables AS tbl INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id AND 1 =idx.is_primary_key LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id AND idxcol.column_id = clmns.column_id AND idxcol.object_id = clmns.object_id AND 0 = idxcol.is_included_column LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id AND typ.user_type_id = typ.system_type_id LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id=clmns.default_object_id LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id AND exprop.minor_id = clmns.column_id WHERE (tbl.name = @TableName and exprop.class = 1) --I don't wand to include comments on indexes ORDER BY clmns.column_id ASC PRINT '</tr></table>' FETCH NEXT FROM Tbls INTO @TableName END PRINT '</body></HTML>' CLOSE Tbls DEALLOCATE Tbls
Take your two largest tables (in terms of columns) and create a data dictionary. Use appropriate...
(SQL) Write DDL statements to create the tables below and use appropriate data types for the attributes.Relational database schema: Teacher = [CourseN, Quarter,TeacherName] Course = [CourseN,CourseName, Nunit) LocationNTime = [CourseN, Quarter , DayTime, RoomN] . //Examples of DayTime: M2:00AM, W4:50PM, and T8:00PM. Note that DayTime is represented as a string. Student = [studentName, CourseN, Quarter] .................... The DDL statement must include at least the following constraints: Every Primary Key; Every Foreign Key; For every Foreign Key constraint, the referential integrity...
Lesson 10Create the DEPARTMENT tables based on the following: Column Name ID Name Data Type Number Varchar2 Length 7 25 Populate the DEPARTMENT table with data from the DEPT table. Include only columns that you need. Create the EMPLOYEE table based on the following table chart: Column Name ID LAST_NAME FIRST_NAME DEPT_ID Data Type Number Varchar2 Varchar2 Number Length 7 25 25 7 Modify the EMPLOYEE table to allow for longer employee last names. Confirm your modification. Confirm that both the...
Create a DFD diagram 0, and Data dictionary for the information system at a typical library. Create a solution as realistic as possible. Data dictionary should contain information about all data elements. In your solution include at least: - 10 processes, - 3 data stores, - 3 external entity instructions: don't use any online diagram or copy paste from internet. Do your own
Sample data for these tables are shown in Figures 3-26 and 3-27. For each SQL statement you write, show the results based on these data. If possible, run the statements you write for the questions that follow in an actual DBMS, as appropriate, to obtain results. Use data types that are consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types by using either the MySQL, Microsoft SQL Server, or Oracle...
A small surgery center needs your help to create a database. The office manager has identified the following types of data (entities): patients, doctors, procedures and appointments. Please identify the attributes and primary keys needed for each of the entities. Identify the data types you would use for each of the attributes. Determine the relationships between the entities and find the one-to-many and many-to-many relationships. Use the ER matrix to determine the relationships. Create an ER diagram using Visio based...
1. Create a few tables using mysql workbench, try to add data then take a screenshot
Describe briefly a database application case of your choice and represent your data in terms of 3 linked (via foreign key/s) tables. List their respective primary key and foreign key/s if any. Your database application case should be different from those already covered in the lectures or practicals. For the database case in the Additional Exercises of the previous practical, complete the proper construction of all the 5 tables in SQL, building all the pertinent primary and foreign keys there....
need help Create or edit the SQL statements required to accomplish the following tasks. All columns should have a column name, feel free to create appropriate aliases Use screen shots to ca evidence that each task is completed; include the SQL records before and after statements are executed, as appropriate. Include all work, even if you get errors. Paste your screen shots into a single PowerPoint file pture statements output from the statements, and views of the object explorer when...
State Lookup Table: Below are two tables, stateStrings (50 rows by 6 columns) and stateInts (50 rows by 3 columns), that contain data for the 50 states. Below are examples for the first state, Alabama: stateStrings: State Abbrev Name Capital Largest City Governor Political Party AL Alabama Montgomery Birmingham Robert Julian Bentley Republican stateInts: State Index Population Electoral Votes 0 4,858,979 9 Write a java application that uses the full tables below to look up the corresponding data using the...
Use Random number generator (under Data Analysis) to create THREE columns of data each 20 points long. -For the FIRST data set use the following parameters: number of variables (2), number of data point (20), Distribution (Normal), Mean (20), Standard Deviation (5), Random seed (1234). Keep these data in columns labeled A and B. -For the SECOND data set use the following parameters: number of variables (1), number of data point (20), Distribution (Normal), Mean (30), Standard Deviation (5), Random...