Database schema for structured query language (SQL) Server

• US 20050027725A1
• Filed: 08/17/2004
• Published: 02/03/2005
• Est. Priority Date: 11/17/2000
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Thompson, John Patrick, Kagalwala, Raxit A.
• Granted Patent: US 7,653,652 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A schema for a SQL (structured query language) database defines classes, properties, methods, and associations.

Claims

1. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

at least one database objects class that represents components of a database system;

at least one application system class that represents installation parameters of the database system;

at least one security class that represents security features pertaining to use of the database system; and

at least one physical storage class that represents physical files and file groups used by the database system to store data.

2. A data structure as recited in claim 1, wherein:

the database system comprises a SQL (structured query language) database;

the database objects class comprises multiple classes representing tables, views, stored procedures, indexes, constraints, and keys;

the application system class comprises multiple classes representing settings, services, and transaction and error logs used by the database system; and

the security class comprises multiple classes representing users, roles, authentication login, and permissions for the users and the roles.

3. A data structure as recited in claim 2, wherein permissions are modeled as association classes between database objects class and the users and roles.

4. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL setting class to represent settings that are used to configure an installation of the database;

a SQL database setting class to represent operational settings for the database;

a SQL configuration value class to represent configuration values;

a SQL language setting class to expose properties of a language record;

a SQL registry setting class to represent the installation and run-time parameters stored in the registry;

a SQL server connection setting class to represent default connection settings;

a SQL objects class to represent objects in a SQL database;

a SQL database class to represent instances of the SQL database;

a SQL server class to represent instances of a SQL server;

a SQL server configuration value class to associate a SQL server installation and the configured value settings for the installation;

a SQL server language setting class to associate a SQL server installation and its language settings;

a SQL server registry setting class to associate a SQL server installation and its registry setting; and

a SQL server database class to associate a SQL server installation and a database that is part of the installation.

5. A data structure as recited in claim 4, wherein the SQL objects class comprises at least one of the following properties:

a name property that defines a label by which an object in the objects class is known; and

a status property to indicate a current status of the object.

6. A data structure as recited in claim 4, wherein the SQL database class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a create-for-attach property to control database file creation;

a database status property to indicate a current operational status on the database;

a name property to define a label by which an object is known;

a primary file path property to return a path and name of an operating system directory containing a primary file for the database;

a size property to expose a total size of the database;

a space available property that returns an amount of disk resource allocated and unused; and

a status property to indicate a current status of the object.

7. A data structure as recited in claim 4, wherein the a SQL server class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a name property to define a label by which an object is known;

a status property to indicate a current status of the object; and

a user profile property to return a high-level role description for a login used by a current connection.

8. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL error log class to represent at least one error log;

a SQL error log entry class to represent entries in the error log;

a SQL transaction log to represent a transaction log in the SQL database;

a SQL objects class to represent objects in the SQL database;

a SQL database class to represent instances of the SQL database;

a SQL server class to represent instances of a SQL Server;

a SQL error log error log entry class to represent an association between the error log and an entry in the error log;

a SQL server error log class to represent an association between a server installation and the error log used by the installation;

a SQL error log data file class to represent an association between the error log and an operating system file used to store the error log;

a SQL transaction log data file class to represent an association between the transaction log and an operating system file that is used to store the log;

a SQL database transaction log class to represent an association between the database and the transaction log for the database; and

a SQL server database class to associate a SQL server installation and a database that is part of the installation.

9. A data structure as recited in claim 8, wherein the SQL error log class comprises at least one of the following properties:

a last modified property to indicate a time and date that the error log was last modified; and

a status property to indicate a current status of an object in the database.

10. A data structure as recited in claim 8, wherein the SQL objects class comprises at least one of the following properties:

a name property that defines a label by which an object in the objects class is known; and

a status property to indicate a current status of the object.

11. A data structure as recited in claim 8, wherein the SQL database class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a create-for-attach property to control database file creation;

a database status property to indicate a current operational status on the database;

a name property to define a label by which an object is known;

a primary file path property to return a path and name of an operating system directory containing a primary file for the database;

a size property to expose a total size of the database;

a space available property that returns an amount of disk resource allocated and unused; and

a status property to indicate a current status of the object.

12. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL objects class to represent objects in a database;

a SQL database class to represent instances of a SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL column class to represent a column in the table;

a SQL trigger class to represent a trigger to be executed when a specified data modification is attempted on the table;

a SQL user defined function class to represent a user defined function in the SQL database

a SQL stored procedure class to represent stored procedures defined in the SQL database;

a SQL view class to represent view tables in the SQL database;

a SQL stored procedure parameter class 814 to represent input and output parameters of a stored procedure;

a SQL table column class to represent an association between the table and the column contained in the table;

a SQL table trigger class to represent an association between the table and the trigger defined for the table;

a SQL database table class to represent an association between the database and the table contained in the SQL database;

a SQL database user defined function class to represent an association between the database and the user-defined function defined within the SQL database;

a SQL database view class to represent an association between the SQL database and the view;

a SQL database stored procedure class to represent an association between the database and a stored procedure defined within the database; and

a SQL stored procedure stored procedure parameter class 832 associates a stored procedure to a parameter used in the stored procedure.

13. A data structure as recited in claim 12, wherein the SQL objects class comprises at least one of the following properties:

a name property that defines a label by which an object in the objects class is known; and

a status property to indicate a current status of the object.

14. A data structure as recited in claim 12, wherein the SQL database class comprises at least one of the following properties:

a collation property to specify a column-level collation of a string datatype in the database;

a create-for-attach property to control database file creation;

a database status property to indicate a current operational status on the database;

a name property to define a label by which an object is known;

a primary file path property to return a path and name of an operating system directory containing a primary file for the database;

a size property to expose a total size of the database;

a space available property that returns an amount of disk resource allocated and unused; and

a status property to indicate a current status of the object.

15. A data structure as recited in claim 12, wherein the SQL table class comprises at least one of the following properties:

an attributes property to indicate various aspects of the table;

a database name property to indicate a name of the database of which the table is part;

a data space used property to report storage space used by rows of the table;

an index space used property that returns a quantity of disk resource used to store indexes;

a rows property that returns a number of rows in the table; and

a status property to indicate a current status of the table.

16. A data structure as recited in claim 12, wherein the SQL column class comprises at least one of the following properties:

a computed property that indicates whether the column is computed based on other values in the database;

a datatype property that indicates a datatype for the column;

an identity property that indicates whether the column is an identity column for the table;

a length property that indicates a maximum number of characters or bytes accepted by the column;

a table name property that indicates a name of the table that a key is defined in; and

a status property to indicate a current status of the table.

17. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL objects class to represent objects in a database;

a SQL column class to represent a column in the table;

a SQL index class to represent an index for a table; and

a SQL index column class to represent an association between the index and the column.

18. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL objects class to represent objects in a database;

a SQL constraint class to represent constraints defined in the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL default class to represent attributes of a single SQL server default;

a SQL column class to represent a column in the table;

a SQL database class to represent instances of a SQL database;

a SQL datatype class to represent datatypes defined in a SQL server installation, the SQL datatype class having two subclasses that represent user data and system data;

a SQL check class to represent check attributes of a SQL server integrity constraint;

a SQL rule class to represent a single data-integrity rule;

a SQL table check class to represent an association between the table and the check attributes defined for the table;

a SQL user datatype default class to represent an association between a user-defined datatype and a rule bound to the column;

a SQL database default class to represent an association between the database and the defaults defined within the database;

a SQL column rule class to represent an association between the column and the rule bound to the column;

a SQL column datatype class to associate the column with its data type;

a SQL database rule class to represent an association between the database and the rules defined within the database;

a SQL user datatype rule class represents an association between the user defined datatype and the rule bound to the column;

a SQL database datatype class associates the database to the datatypes defined within the database;

a SQL base datatype class represents an association between the user-defined datatype and the system datatype; and

a SQL column default class to associate the column to the default for the column.

19. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL objects class to represent objects in a database;

a SQL constraint class to represent constraints defined in the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL column class to represent a column in the table;

a SQL database class to represent instances of a SQL database;

a SQL key class to represent keys defined for the table, the SQL key class having two subclasses that represent candidate keys in the table and foreign keys in the table, the candidate key subclass having two subclasses that represent unique keys and primary keys;

a SQL key column class to represent an association between a key and the column that is part of the key;

a SQL table key class to represent an association between the table and the key defined for the table;

a SQL referenced table class to represent an association between the foreign key and the table that contains the primary key referenced by the foreign key;

a SQL database candidate key class to represent an association between the database and the candidate key that is present in one of the tables in the database; and

a SQL referenced key class to represent an association between the foreign key and the candidate key that the foreign key references.

20. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL setting class to represent settings that are used to configure an installation of the database;

a SQL objects class to represent objects in a SQL database;

a SQL database class to represent instances of the SQL database;

a SQL server class to represent instances of a SQL server;

a SQL integrated security setting class to represent integrated security settings;

a SQL DBMS user object class to represent objects related to user authentication;

a SQL user class to represent users of the SQL database;

a SQL login class to represent logins of the SQL database;

a SQL role class to represent groups of users with similar security attributes of the SQL database, the SQL role class having two subclasses to represent a SQL server security role not constrained to operation within a single database and to represent properties of a SQL server database role;

a SQL server integrated security setting class to represent an association between a SQL server installation and its security settings;

a SQL server login class to represent an association between the SQL server and a login defined within the SQL Server;

a SQL server server role class to represent an association between the SQL server and server roles defined within the SQL Server;

a SQL server user class to represent an association between the SQL server and a database user;

a SQL database login class to represent an association between a database and a login that is mapped to a user defined in the database;

a SQL database owner login class to represent an association between a database and the login mapped to the user that owns the database;

a SQL user login class to represent an association between a database user and the login used to authenticate the user;

a SQL member login class to represent an association between a SQL Server role and a login that is a member of the role;

a SQL login default database class to represent an association between a login and the default database for the login;

a SQL member user class to represent an association between a database role and a user that is a member of the role;

a SQL DBMS object owner class to represent an association between a SQL server database object and the user who owns the object;

a SQL database user class to represent an association between a database and a user defined for the database;

a SQL member database role class to associate two database roles; and

a SQL database database role class to associate a database role to the database within which the role is defined.

21. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL objects class to represent objects in a SQL database;

a SQL database class to represent instances of the SQL database;

a SQL stored procedure class to represent stored procedures defined in the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL user defined function class to represent a user defined function in the SQL database

a SQL view class to represent view tables in the SQL database;

a SQL DBMS user object class to represent objects related to user authentication;

a SQL user class to represent users of the SQL database;

a SQL role class to represent groups of users with similar security attributes of the SQL database, the SQL role class having two subclasses to represent a SQL server security role not constrained to operation within a single database and to represent properties of a SQL server database role;

a SQL database role database permission class to represent permissions that a database role has for the database in which it is defined;

a SQL user database permission class to represent permissions granted to a user for a database;

a SQL user stored procedure permission class to represent permissions granted to a user for a stored procedure;

a SQL database role stored procedure permission class to represent permissions that a database role has for a stored procedure;

a SQL user table permission class to represent permissions granted to a user for a table;

a SQL database role table permission to represent the permissions that a database role has for a table;

a SQL user defined function permission to represent permissions granted to a user for a stored procedure;

a SQL database role user defined function permission to represent permissions that a database role has for a table; and

a SQL user view permission to represent permissions granted to a user for a view.

22. A data structure stored on one or more computer-readable media that is instantiated in accordance with a schema, the schema comprising:

a SQL database class to represent instances of the SQL database;

a SQL table class to represent at least one table in the SQL database;

a SQL index class to represent an index for a table; and

a SQL key class to represent keys defined for the table, the SQL key class having a subclass that represents candidate keys in the table;

a SQL file group class to represent attributes of a SQL server file group;

a SQL extension class to represent extensions made via associations to a managed system element;

a SQL extends class to associate a first class with a second class that extends the first class by defining new properties and methods;

a SQL file group database file class to associate a database file group to operating system files that are part of the group;

a SQL index file group class to represent an association between an index and a file group that stores the index;

a SQL key file group class to represent an association between a key and a file group used to store the key;

a SQL table file group class to represent an association between a table and file groups used to store the table;

a SQL table text file group class to associate a table with a file group; and

a SQL database file group class to represent an association between a database and a file group that contains operating system files that store data for the database.

23. An operating system comprising:

an object-oriented management service to monitor, configure, and control systems, services, and applications;

a database schema for a SQL (structured query language) database maintained by the object-oriented service, the database schema comprising;

database objects classes that represent components of a SQL database;

application system classes that represent settings, services, and transaction and error logs used by the SQL database;

security classes that represent users, roles, authentication login, and permissions for the users and the roles to use the SQL database; and

physical storage classes that represent physical files and file groups used by the database system to store data.

24. An operating system as recited in claim 23, wherein:

the database objects class comprises multiple classes representing tables, views, stored procedures, indexes, constraints, and keys;

the application system class comprises multiple classes representing settings, services, and transaction and error logs used by the database system; and

the security class comprises multiple classes representing users, roles, authentication login, and permissions for the users and the roles.

25. An operating system as recited in claim 24, wherein permissions are modeled as association classes between database objects class and the users and roles.

26. An operating system as recited in claim 23, wherein the database objects classes comprises at least one of the following classes:

an objects class to represent objects in the SQL database;

a database class to represent instances of the SQL database;

a server class to represent instances of a SQL server;

a table class to represent at least one table in the SQL database;

a column class to represent a column in the table;

a key class to represent keys defined for the table;

a user defined function class to represent a user defined function in the SQL database;

a stored procedure class to represent stored procedures defined in the SQL database; and

a view class to represent view tables in the SQL database.

27. An operating system as recited in claim 23, wherein the application system classes comprises at least one of the following classes:

a setting class to represent settings that are used to configure an installation of the SQL database;

a database setting class to represent operational settings for the SQL database;

a configuration value class to represent configuration values;

a language setting class to expose properties of a language record;

a registry setting class to represent the installation and run-time parameters stored in the registry; and

a server connection setting class to represent default connection settings.

28. An operating system as recited in claim 23, wherein the security classes comprises at least one of the following classes:

an integrated security setting class to represent integrated security settings;

a user class to represent users of the SQL database;

a login class to represent logins of the SQL database; and

a role class to represent groups of users with similar security attributes of the SQL database.

29. An operating system as recited in claim 23, wherein the physical storage classes comprises at least one of the following classes:

a file group class to represent attributes of a SQL server file group;

a extension class to represent extensions made via associations to a managed system element;

a extends class to associate a first class with a second class that extends the first class by defining new properties and methods;

a file group database file class to associate a database file group to operating system files that are part of the group;

a index file group class to represent an association between an index and a file group that stores the index;

a key file group class to represent an association between a key and a file group used to store the key;

a table file group class to represent an association between a table and file groups used to store the table;

a table text file group class to associate a table with a file group; and

a database file group class to represent an association between a database and a file group that contains operating system files that store data for the database.

30. A method comprising:

creating a data structure in accordance with a schema, the schema defining at least one database objects class that represents components of a database system, at least one application system class that represents installation parameters of the database system, at least one security class that represents security features pertaining to use of the database system, and at least one physical storage class that represents physical files and file groups used by the database system to store data; and

populating the data structure.

31. A data structure embodied on one or more computer-readable media that is produced as a result of the method as recited in claim 30.

32. A system comprising:

means for instantiating a data structure in accordance with a schema, the schema defining at least one database objects class that represents components of a database system, at least one application system class that represents installation parameters of the database system, at least one security class that represents security features pertaining to use of the database system, and at least one physical storage class that represents physical files and file groups used by the database system to store data; and

means for populating the data structure.