SQL Data Modeling best practices

what is data modeling in sql server and sql data modeling interview questions and answers pdf free download
JackBrown Profile Pic
JackBrown,Georgia,Professional
Published Date:12-07-2017
Your Website URL(Optional)
Comment
MarkLogic Server SQL Data Modeling Guide 1 MarkLogic 9 May, 2017 Last Revised: 9.0-1, May, 2017 Copyright © 2017 MarkLogic Corporation. All rights reserved.MarkLogic Server SQL on MarkLogic Server 1.0 SQL on MarkLogic Server 11 The views module is used to create and manage SQL schemas and views. The main topics in this chapter are: • Terms Used in this Guide • Schemas and Views • Template View Security • Example Template View 1.1 Terms Used in this Guide The following are the definitions for the terms used in this guide: • A view is a representation of a SQL view. A view is an XML document in the Schemas database and consists of a unique name (which must be unique in the context of a particular schema) and a sequence of column specifications. There are two types of views: template views and range views. • A schema is a representation of a SQL schema. A schema is implemented as an XML document in the Schemas database and consists of a unique name (which must also be unique) and a collection of views. During SQL execution, the schema provides the naming context for its views, which enables you to have multiple views of the same name in different schemas. The default schema is called “main.” It is default in the sense that it is always implicitly available and first on the default schema search path for name resolution in SQL. Even though the “main” schema is a default, you must create this schema. • A column in a view has a name, SQL datatype, and a value that identifies a particular document element or property. • A view scope is used to constrain the subset of the database to which the view applies. A view scope can either limit rows in the view to documents with a specific element (localname + namespace), to documents in a particular directory, or to documents in a particular collection. • Template Driven Extraction (TDE) is the method used to map documents in a MarkLogic database to SQL views. Note: You must have the tde-admin and any-uri roles to create template views and the view-admin role to create range views. MarkLogic 9—May, 2017 SQL Data Modeling Guide—Page 4MarkLogic Server SQL on MarkLogic Server 1.2 Schemas and Views Schemas and views are the main SQL data-modeling components used to represent content stored in a MarkLogic Server database to SQL clients. A view is a virtual read-only table that represents data stored in a MarkLogic Server database. Each column in a view is based on an index in the content database, as described in “Example Template View” on page 9. User access to each view is controlled by a set of permissions, as described in “Template View Security” on page 8. There are two types of views: • template views: Views that are created by Template Driven Extraction (TDE templates). template views are inserted as documents into the schema database associated with the content database. When inserted into a schema database, template views automatically creates triple data in the content database for each column defined in the template and all of the documents are reindexed. Template views can also be created to extract existing triples in documents, rather than elements. • range views: Views that are based on range indexes and fields. Each column in a view is based on a range index or field in the content database. You must create the range indexes and fields in the content database before creating a range view. Unlike template views, range views allow you to add and remove columns on the view. Note: In most situations, you will want to create a template view. Though a range view may be preferable to a template view in some situations, such as for a database already configured with range indexes, they are supported mostly for backwards compatibility with previous versions of MarkLogic. For this reason, most of the dicussion in this guide will be on the use of template views. For details on range views, see “Creating Range Views” on page 50. A schema is a naming context for a set of views and user access to each schema can be controlled with a different set of permissions. Each view in a schema must have a unique name. However, you can have multiple views of the same name in different schemas. For example, you can have three views, named ‘Songs,’ each in a different schema with different protection settings. MarkLogic 9—May, 2017 SQL Data Modeling Guide—Page 5MarkLogic Server SQL on MarkLogic Server Each view has a scope that defines the documents from which it reads the column data. The view scope constrains the view to documents located in a particular directory (template views only), or to documents in a particular collection. The figure below shows a schema called ‘main’ that contains two views, each with a different view scope. The view “Songs” is constrained to documents that are in the http://view/songs collection and the view “Names” is constrained to documents that are located in the /my/directory/ directory. Documents my:song my:titleA Day in the Life/my:title Schema: main my:albumSgt. Pepper/my:album my:year1967/my:year /my:song View: “Songs” your:song scope: http://view/songs your:titleA Day w/o Me/your:title Collection: your:albumBoy/your:album http://view/songs your:year1980/your:year /your:song my:song my:titleWhat it is/my:title my:albumSailing to Phil/my:album View: “Names” my:year2000/my:year scope: /my/directory/ /my:song my:name my:fnameBruce/my:fname Directory: template views only my:mnameBucky/my:mname /my/directory/ my:lnameBanner/my:lname /my:name As described above, schemas and views are stored as documents in the schema database associated with the content database for which they are defined. The default schema database is named ‘Schemas.’ If multiple content databases share a single schema database, each content database will have access to all of the views in the schema database. MarkLogic 9—May, 2017 SQL Data Modeling Guide—Page 6MarkLogic Server SQL on MarkLogic Server For example, in the figure below, you have two content databases, Database A and Database B, that both make use of the Schemas database. In this example, you create a single schema, named ‘main,’ that contains two views, View1 and View2, on Database A. You then create two views, View3 and View4, on Database B and place them into the ‘main’ schema. In this situation, both Database A and Database B will each have access to all four views in the ‘main’ schema. Content Database A Content Database B Create Create main schema View3 and View4 Create View1 and View2 Schemas Database Schema: main View1 View2 View3 View4 A more “relational” configuration is to assign a separate schema database to each content database. In the figure below, Database A and Database B each have a separate schema database, SchemaA and SchemaB, respectively. In this example, you create a ‘main’ schema for each content database, each of which contains the views to be used for its respective content database. Content Database B Content Database A Create Create main schema main schema Create Create View1 and View2 View3 and View4 SchemaB Database SchemaA Database Schema: main Schema: main View3 View1 View2 View4 MarkLogic 9—May, 2017 SQL Data Modeling Guide—Page 7MarkLogic Server SQL on MarkLogic Server 1.3 Template View Security The tde-admin and any-uri roles are required in order to insert a template document into the schema database. The tde-view role is required to access a template view. Access to views can be further restricted by setting additional permissions on the template document that defines the view. Since the same view can be declared in multiple templates loaded with different permissions, the access to views should be controlled at the column level. Column level read permissions are implicit and are derived from the read permissions set on the template documents. Permissions on a column are not required to be identical and are ORed together. A user with a role that has at least one of the read permissions set on a column will be able to see the column. If a user does not have permissions on any of the view's columns, the view itself is not visible. For example, there are two views, as illustrated below. • The View1 template document is configured for Columns C1 and C2 was loaded with P1 Permissions. • The View2 template document is configured for Columns C1 and C3 was loaded with P2 Permissions. John has P1 Permissions, so he can see Columns C1 and C2. Chris has both P1 and P2 Permissions, so he can see Columns C1, C2, and C3. Mary has P2 Permissions, so she can see Columns C1 and C3. For details on how to set document permissions, see Protecting Documents in the Security Guide. MarkLogic 9—May, 2017 SQL Data Modeling Guide—Page 8MarkLogic Server SQL on MarkLogic Server Chris John Mary P1 and P2 Permission P1 Permission P2 Permission Can see C1, C2, and C3 Can see C1 and C2 Can see C1 and C3 Schema: main View1 View2 C1 C1 C3 C2 P1 Permission P2 Permission Schemas Database Note: The MarkLogic SQL engine does not support documents that make use of element-level security. Any document containing protected elements will be skipped by the indexer. 1.4 Example Template View This section provides an example document and a template view used to extract data from the document and present it in the form of a view. Consider a document of the following form: book title subject="oceanography"Sea Creatures/title pubyear2011/pubyear keywordscience/keyword author nameJane Smith/name universityWossamotta U/university /author body name type="cephalopod"Squid/name Fascinating squid facts... name type="scombridae"Tuna/name Fascinating tuna facts... name type="echinoderm"Starfish/name Fascinating starfish facts... /body /book MarkLogic 9—May, 2017 SQL Data Modeling Guide—Page 9MarkLogic Server SQL on MarkLogic Server The following template extracts each element and presents it as a column in a view, named ‘book’ in the ‘main’ schema. template xmlns="http://marklogic.com/xdmp/tde" context/book/context rows row schema-namemain/schema-name view-namebook/view-name columns column nametitle/name scalar-typestring/scalar-type valtitle/val /column column namepubyear/name scalar-typedate/scalar-type valpubyear/val /column column namekeyword/name scalar-typestring/scalar-type valkeyword/val /column column nameauthor/name scalar-typestring/scalar-type valauthor/name/val /column column nameuniversity/name scalar-typestring/scalar-type valauthor/university/val /column column namecephalopod/name scalar-typestring/scalar-type valbody/nametype="cephalopod"/val /column column namescombridae/name scalar-typestring/scalar-type valbody/nametype="scombridae"/val /column column nameechinoderm/name scalar-typestring/scalar-type valbody/nametype="echinoderm"/val /column /columns /row MarkLogic 9—May, 2017 SQL Data Modeling Guide—Page 10MarkLogic Server SQL on MarkLogic Server Quick Start 2.0 SQL on MarkLogic Server Quick Start 33 This chapter describes how to set up your MarkLogic Server for SQL. This chapter describes how to set up a typical development environment in which the SQL client and MarkLogic Server are configured on the same machine. For a production environment, you would typically configure your SQL client and MarkLogic Server on separate machines. Note: You must have the admin role on MarkLogic Server to complete the procedures described in this chapter. The main topics in this chapter are: • Setup MarkLogic Server • Load the Data • Create Template Views • Enter SQL Queries to Test • Using MLSQL 2.1 Setup MarkLogic Server Install MarkLogic Server on the database server, as described in the Installation Guide. and follow these procedures: • Create a Schema Database and a SQL Database • Create an ODBC App Server 2.1.1 Create a Schema Database and a SQL Database How to create a database is described in detail in Creating a New Database in the Administrator’s Guide. This section provides a quick-start procedure for creating the database used in this example. Warning Every SQL database must have its own separate schema database. 1. Open your browser and navigate to the Admin Interface: http://hostname:8001 Where hostname is the name of your MarkLogic Server host machine. 2. Click the Forests icon in the left tree menu. MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 12MarkLogic Server SQL on MarkLogic Server Quick Start 3. Click the Create tab at the top right. The Create Forest page displays. Enter ‘SQLschemas’ as the name of your forest in the Forest Name textbox. Click OK. 4. Click the Create tab at the top right. The Create Forest page displays. Enter ‘SQLdata’ as the name of your forest in the Forest Name textbox. Click OK. 5. Click the Databases icon in the left tree menu. MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 13MarkLogic Server SQL on MarkLogic Server Quick Start 6. Click the Create tab at the top right. The Create Database page displays. Enter ‘SQLschemas’ as the name of the new database and click Ok: 7. At the top of the page click Database-Forests 8. Check the SQLschemas box to attach the SQLschemas forest. Click Ok: MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 14MarkLogic Server SQL on MarkLogic Server Quick Start 9. Click the Create tab at the top right. The Create Database page displays. Enter ‘SQLdata’ as the name of the new database and select ‘SQLschemas’ as the Schema Database. 10. Scroll down the Create Database page to the Triple Index setting and click ‘true’ to enable triple indexing. Click Ok: 11. At the top of the page click Database-Forests MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 15MarkLogic Server SQL on MarkLogic Server Quick Start 12. Check the SQLdata box to attach the SQLdata forest. Click Ok: MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 16MarkLogic Server SQL on MarkLogic Server Quick Start 2.1.2 Create an ODBC App Server Schemas and views represent content stored in a MarkLogic Server database. Each content database used by a SQL client is managed by an ODBC App Server that accepts SQL queries from the SQL client and responds by returning MarkLogic Server data in tuple form. An ODBC App Server can manage only one content database. However, a single content database can be managed by multiple ODBC App Servers. ODBC App Servers are described in detail in the ODBC Servers chapter in the Administrator’s Guide. Open the Admin Interface To create a new server, complete the following steps: 1. Click the Groups icon in the left tree menu. 2. Click the group in which you want to define the ODBC server (for example, Default). 3. Click the App Servers icon on the left tree menu. 4. Click the Create ODBC tab at the top right. The Create ODBC Server page will display: MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 17MarkLogic Server SQL on MarkLogic Server Quick Start 5. In the Server Name field, enter a shorthand name for this ODBC server. In this example, the name of the App Server is ‘SQL.’ 6. In the Root directory field, enter /. 7. In the Port field, enter the port number through which you want to make this ODBC server available. The default PostgreSQL listening socket port is 5432. 8. Leave the Modules field as (file system). 9. In the Database field, select the ‘SQLdata’ database you created in “Create a Schema Database and a SQL Database” on page 12. MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 18MarkLogic Server SQL on MarkLogic Server Quick Start 2.2 Load the Data This section describes the procedure for loading the sample documents. 1. Go to the following URL to open Query Console: http://hostname:8000/qconsole/ Where hostname is the name of your MarkLogic Server host. 2. Select the SQLdata database from the Content Source pulldown menu and JavaScript from the Query Type menu. 3. Cut and paste the following XQuery into Query Console: declareUpdate(); xdmp.documentInsert( "/employee1.json", "Employee": "ID": 1, "FirstName": "John", "LastName": "Widget", "Position": "Manager of Human Resources" ), xdmp.documentInsert( "/employee2.json", "Employee": "ID": 2, "FirstName": "Jane", "LastName": "Lead", "Position": "Manager of Widget Research" ), xdmp.documentInsert( "/employee3.json", "Employee": "ID": 3, "FirstName": "Steve", "LastName": "Manager", "Position": "Senior Technical Lead" ), xdmp.documentInsert( "/employee4.json", "Employee": "ID": 4, "FirstName": "Debbie", "LastName": "Goodall", "Position": "Senior Widget Researcher" ), xdmp.documentInsert( MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 19MarkLogic Server SQL on MarkLogic Server Quick Start "/employee5.json", "Employee": "ID": 14, "FirstName": "Lori", "LastName": "Baker", "Position": "Senior Wingnut" ), xdmp.documentInsert( "/employee6.json", "Employee": "ID": 15, "FirstName": "Steve", "LastName": "Lostit", "Position": "Mad Scientist" ), xdmp.documentInsert( "/employee7.json", "Employee": "ID": 16, "FirstName": "Donald", "LastName": "Putin", "Position": "Power Couple" ), xdmp.documentInsert( "/expense1.json", "Expenses": "EmployeeID": 1, "Date": "2012-06-27", "Amount": 131.02, "Purchase": "Category": "Lodging", "Vendor": "Hyatt Hotels", "Description": "Exec. King Room"), xdmp.documentInsert( "/expense2.json", "Expenses": "EmployeeID": 2, "Date": "2012-06-27", "Amount": 155.22, "Purchase": "Category": "Transportation", "Vendor": "Alaska", "Description": "SFO SEA"), xdmp.documentInsert( "/expense3.json", "Expenses": "EmployeeID": 1, "Date": "2012-08-03", "Amount": 59.95, "Purchase": "Category": "Meals", "Vendor": "Doug's Dinner", "Description": "Dinner"), xdmp.documentInsert( "/expense4.json", "Expenses": "EmployeeID": 3, MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 20MarkLogic Server SQL on MarkLogic Server Quick Start "Date": "2012-05-07", "Amount": 162.95, "Purchase": "Category": "Lodging", "Vendor": "Hilton Hotels", "Description": "Exec. Suite"), xdmp.documentInsert( "/expense5.json", "Expenses": "EmployeeID": 3, "Date": "2012-05-30", "Amount": 120.00, "Purchase": "Category": "Lodging", "Vendor": "Kingsman Motel", "Description": "Reg Room"), xdmp.documentInsert( "/expense6.json", "Expenses": "EmployeeID": 4, "Date": "2012-03-23", "Amount": 155.55, "Purchase": "Category": "Lodging", "Vendor": "Waterfront Hotel", "Description": "Queen Room"), xdmp.documentInsert( "/expense7.json", "Expenses": "EmployeeID": 4, "Date": "2012-06-05", "Amount": 104.29, "Purchase": "Category": "Meals", "Vendor": "Good Eats", "Description": "Client Lunch"), xdmp.documentInsert( "/GoodEats.json", "ApprovedVendor": "Name": "Good Eats", "Address": "Street": "707 Oxford Rd.", "City": "Ann Arbor", "Region": "MI", "PostalCode": "48104", "PostalCode": "USA", "Phone": "(313) 555-5735"), xdmp.documentInsert( "/WaterfrontHotel.json", "ApprovedVendor": "Name": "Waterfront Hotel", "Address": "Street": "1000 Coast Rd.", "City": "Santa Cruz", MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 21MarkLogic Server SQL on MarkLogic Server Quick Start "Region": "CA", "PostalCode": "94330", "PostalCode": "USA", "Phone": "(831) 745-8913"), xdmp.documentInsert( "/KingsmanMotel.json", "ApprovedVendor": "Name": "Kingsman Motel", "Address": "Street": "4832 Frankster St.", "City": "Renor", "Region": "NV", "PostalCode": "88660", "PostalCode": "USA", "Phone": "(702) 436-3785"), xdmp.documentInsert( "/Hilton.json", "ApprovedVendor": "Name": "Hilton Hotels", "Address": "Street": "555 Market St.", "City": "San Francisco", "Region": "CA", "PostalCode": "94033", "PostalCode": "USA", "Phone": "(415) 540-8732"), xdmp.documentInsert( "/Hyatt.json", "ApprovedVendor": "Name": "Hyatt Hotels", "Address": "Street": "9023 Caterberry Ave.", "City": "Seattle", "Region": "WA", "PostalCode": "56445", "PostalCode": "USA", "Phone": "(206) 321-3152"), xdmp.documentInsert( "/MealLimit.json", "ExpenseLimit": "Category": "Meals", "Limit": 100), xdmp.documentInsert( "/LodgingLimit.json", "ExpenseLimit": "Category": "Lodging", "Limit": 300), xdmp.documentInsert( "/TransLimit.json", "ExpenseLimit": "Category": "Transportation", "Limit": 200) MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 22MarkLogic Server SQL on MarkLogic Server Quick Start 4. In the control bar below the query window, click Run: MarkLogic 9ea—First Half, 2017 SQL Data Modeling Guide—Page 23

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.