HomeworkLib
Question

Take your two largest tables (in terms of columns) and create a data dictionary. Use appropriate...

Take your two largest tables (in terms of columns) and create a data dictionary. Use appropriate data types and lengths for your attributes.

Create DBMS data Dictionary
engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

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
0 0
Add a comment
Know the answer?
Add Answer to:
Take your two largest tables (in terms of columns) and create a data dictionary. Use appropriate...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Review the example Hospital Physician Data Dictionary as found in your Lecture this week. Create a...

    Review the example Hospital Physician Data Dictionary as found in your Lecture this week. Create a similar Data Dictionary for Patients, using the same format. Include at least 5 field names you think should be included in a Patient Data dictionary. Remember to use different data types for dates, text, and numbers. I have given you two examples to start. (10 points) Field Name Definition Field Length Data Type Patient ID Assigned ID code for patient 9 Numeric Patient FName...

  • (SQL) Write DDL statements to create the tables below and use appropriate data types for the...

    (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...

    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 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...

    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...

    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

    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...

    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...

    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...

    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...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT